CODEWITHSUNDEEP
sql-server-2008t-sqlxmltype
SkoolCodeian
SkoolCodeian

Reputation: 147

Efficiently query a XML column in SQL table

We are storing a Untyped XML data into one of our tables. We need to find data based on specific values in certain XML nodes. The XML (simplified) in question is as below.

<Session id="4f3d4a2c-4f3e-4e53-816c-e0e1b10ee5ad" TimeStamp="2015-09-15T12:02:48">
  <Entries>
    <Entry DataItemId="System.Security.User.IsBroker">
        <Value>True</Value>
    </Entry>
    <Entry DataItemId="System.Security.User.IsOfficeAdmin">
        <Value>False</Value>
    </Entry>
    <Entry DataItemId="System.Security.User.BrokerId">
        <Value>32165498</Value>
    </Entry>
    <Entry DataItemId="System.Security.User.DisplayName">
        <Value>Mike Pearsons</Value>
    </Entry>
    <Entry DataItemId="Address.Line1">
        <Value>Long Marston</Value>
    </Entry>
   <Entry DataItemId="Address.Line2">    
       <Value>End Cottage</Value>
   </Entry>
   <Entry DataItemId="Address.Line3">    
       <Value>End Cottage</Value>
   </Entry>
   <Entry DataItemId="Address.Postcode">    
       <Value>End Cottage</Value>
   </Entry>
    .....
    .....
  </Entries>
</Session>

Now we have to search for records where the Address1 is like 'some user entered text.'

Currently we are using the following query to achieve the same

SELECT column1, column2, column 3, XMLColumn
FROM tblXMLData
WHERE 
(XMLColumn.value('(/Session/Entries/Entry[@DataItemId="Address.Line1"]/Value)[1]', 'varchar(210)'))
LIKE ('%sometext%')

Though this is giving us the desired results but I was just wondering if there is more efficient way to achieve this or probably a faster way. The query sometime takes couple of minutes to return the results (from 15k records).

Upvotes: 0

Views: 423

Answers (1)

Code Different
Code Different

Reputation: 93151

You can try this, which is about half the cost of the query you posted. The advantage is that it does not have to convert XML data to SQL data, which lowers the overhead:

DECLARE @Address varchar(210) = UPPER('user entered text')

SELECT  *
FROM    tblXMLData
WHERE   XMLColumn.exist('/Session/Entries/Entry[@DataItemId="Address.Line1" and contains(upper-case(.), sql:variable("@Address"))]') = 1

If you find that you run this type of queries a lot, you can also consider XML index

Upvotes: 1

Related Questions