Extract data matching a condition inside an XML array in SQL server

Question

Considering this simple table id (int), name (varchar), customFields (xml)

customFields contains an XML representation of a C# Dictionary. E.g :

 <dictionary>
   <item>
     <key><int>1</int></key>
     <value><string>Audi</string></value>
   </item>
   <item>
     <key><int>2</int></key>
     <value><string>Red</string></value>
   </item>
 </dictionary>

How do I select all rows of my table with 3 columns: id, name and 1 column that is the content of the string tag where the value of the int tag is equal 1.

The closest to the result I managed to get is:

SELECT id, name, C.value('(value/string/text())[1]', 'varchar(max)')
FROM myTable
OUTER APPLY customFields.nodes('/dictionary/item') N(C)
WHERE (customFields IS NULL OR C.value('(key/int/text())[1]', 'int') = 1)

Problem is, if xml doesn't have a int tag = 1 the row is not returned at all (I would still like to get a row with id, name and NULL in the 3rd column)

I was just in the middle of testing it. And.... it's awesome! I was just checking the execution plan as I'm wondering how that would perform on a big table (i.e. is it worth indexing the Xml field?) — Johann, Jan 27 '15 at 08:36
I don't know if the indexing will help. Indexes are not very helpful when you don't have raw (simple type like int or varchar) data inside of indexed attributes (as in your case), becouse the operations must be performed on that attribute anyway. — Marcin Zablocki, Jan 27 '15 at 10:02

score 4 · Accepted Answer · answered Jan 26 '15 at 17:47

I've created a table the same as yours and this query worked fine:

select id, name,
     (select C.value('(value/string/text())[1]','varchar(MAX)')
      from xmlTable inr outer apply
      customField.nodes('/dictionary/item') N(C)
      where 
      C.value('(key/int/text())[1]','int') = 1
      and inr.id = ou.id) as xmlVal
from xmlTable ou

Here is my result: xml-query

The reason why your query didn't worked is because it first selects values of "value/string" for all rows from "myTable" and then filters them. Therefore, the null values appear only on empty fields, and on the other fields (which contains any xml value), the "value/string/text()" is displayed. This is what your query without where clause returns:

id,name,xml
1   lol NULL
2   rotfl   Audi
2   rotfl   Red
3   troll   Red

Extract data matching a condition inside an XML array in SQL server

1 Answers1