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)

Answer 1

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:

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