Like Search on XML fields - sql server 2008 R2

Question

I have a XML data field in my table (Sql server 2008 R2) where I am saving XML of my product metadata. Below is the format of xml. There could be different numbers of metadata associated with products hence no fixed format.

<Item>
      <Profile>170</Profile>
      <Type>1</Type>
      <InterfaceType DefaultImage="">Product</InterfaceType>
      <Metadatas>
        <Metadata>
          <Control>2ad9cf19-2a88-4b6c-9f59-28e177679d2a</Control>
          <ControlTypeID>5ae9a004-c0de-4692-a9bd-e8f73926f4f3</ControlTypeID>
          <Name>QA Category</Name>
          <Value>test</value>
          <ControlDatas />
        </Metadata>
        <Metadata>
          <Control>8504787e-f502-4e7a-9313-3257df10b554</Control>
          <ControlTypeID>5ae9a004-c0de-4692-a9bd-e8f73926f4f3</ControlTypeID>
          <Name>AS240 Desc</Name>
          <Value>240</value>
          <ControlDatas />
        </Metadata>
        <Metadata>
          <Control>97927910-234c-4827-b7c7-3bcb21dc2a88</Control>
          <ControlTypeID>5ae9a004-c0de-4692-a9bd-e8f73926f4f3</ControlTypeID>
          <Name>Resolution</Name>
          <Value>40</value>
          <ControlDatas />
        </Metadata>
        <Metadata>
          <Control>e3d7a5c8-c8e0-4c44-97ff-4ea290df1c34</Control>
          <ControlTypeID>5ae9a004-c0de-4692-a9bd-e8f73926f4f3</ControlTypeID>
          <Name>Business Center</Name>
          <Value>chicago</value>
          <ControlDatas />
        </Metadata>
        <Metadata>
          <Control>1e3674ec-0b9f-4faa-9454-52c293b8d389</Control>
          <ControlTypeID>f9966e48-3cf0-47f5-a602-0e47d6bc445c</ControlTypeID>
          <Name>QA Spec Status</Name>
          <Value>1</Value>
          <ControlDatas />
        </Metadata>
        <Metadata>
          <Control>d96d3390-63de-414d-869a-9e0243ff4cbc</Control>
          <ControlTypeID>5ae9a004-c0de-4692-a9bd-e8f73926f4f3</ControlTypeID>
          <Name>QA Manager</Name>
          <Value />
          <ControlDatas />
        </Metadata>
        <Metadata>
          <Control>a0fe9e68-c45c-49e2-8385-9f99f1d9e5fe</Control>
          <ControlTypeID>5ae9a004-c0de-4692-a9bd-e8f73926f4f3</ControlTypeID>
          <Name>Product Quality Manager</Name>
          <Value />
          <ControlDatas />
        </Metadata>
        <Metadata>
          <Control>b72dd25e-ac2b-4eca-b59c-b2caec2c0e92</Control>
          <ControlTypeID>5ae9a004-c0de-4692-a9bd-e8f73926f4f3</ControlTypeID>
          <Name>GTIN</Name>
          <Value />
          <ControlDatas />
        </Metadata>
        <Metadata>
          <Control>d9df10f0-a82f-4ba2-96f5-b71774a521c9</Control>
          <ControlTypeID>c349aa0e-9c1d-406a-9eb9-b9ad5e561300</ControlTypeID>
          <Name>Item Creation Date</Name>
          <Value>27/04/2013 12:00:00 AM</Value>
          <ControlDatas />
        </Metadata>
        <Metadata>
          <Control>dd6e875d-34b7-4b79-bb65-c3608f81f754</Control>
          <ControlTypeID>f9966e48-3cf0-47f5-a602-0e47d6bc445c</ControlTypeID>
          <Name>SUPC Status</Name>
          <Value>active</Value>
          <ControlDatas />
        </Metadata>
        <Metadata>
          <Control>a2682372-664b-4ee0-9a72-d69965c9be9f</Control>
          <ControlTypeID>5ae9a004-c0de-4692-a9bd-e8f73926f4f3</ControlTypeID>
          <Name>Qa Spec Desc</Name>
          <Value />
          <ControlDatas />
        </Metadata>
        <Metadata>
          <Control>5c4f7fa4-f56b-4d91-a0dc-ead6f8115020</Control>
          <ControlTypeID>5ae9a004-c0de-4692-a9bd-e8f73926f4f3</ControlTypeID>
          <Name>Brand</Name>
          <Value>Sysco</Value>
          <ControlDatas />
        </Metadata>
        <Metadata>
          <Control>52fc415d-b844-472c-82d9-f205a9104825</Control>
          <ControlTypeID>5ae9a004-c0de-4692-a9bd-e8f73926f4f3</ControlTypeID>
          <Name>UPC</Name>
          <Value />
          <ControlDatas />
        </Metadata>
      </Metadatas>
    </Item>

I want to do a like search on the values of the metadata which are stored in value tag in metadata tag. I am successfully able to do a exact search on string by doing search on

<Name>QA Category</Name>
<Value>test</value>

is there a way to do like search on value tag? There could be millions of product records in database.

score 0 · Answer 1 · edited May 23 '17 at 12:28

query() – Used to extract XML fragments from an XML data type.

value() – Used to extract a single value from an XML document.

exist() – Used to determine if a specified node exists. Returns 1 if yes and 0 if no.

modify() – Updates XML data in an XML data type.

node() – Shreds XML data into multiple rows (not covered in this blog post).

you can do something like: Similar question

SELECT * 
FROM WebPageContent 
WHERE data.value('(/Item/Text)[1]', 'varchar(100)') LIKE 'XYZ%'

OR

You can use something like:

where XMLField.exist('/Item/someitems/Name[.="QA Category"]')=1

As you have lots of records, i would suggest you to create xml index on that column to improve performence

Here is very good article by pinal on xml based searching..

Another good article

Like Search on XML fields - sql server 2008 R2

1 Answers1