title: Extract data matching a condition inside an XML array in SQL server
body: <p>Considering this simple table <code>id (int), name (varchar), customFields (xml)</code></p>
  
  <p>customFields contains an XML representation of a C# Dictionary. E.g :</p>
  
  <pre><code> &lt;dictionary&gt;
     &lt;item&gt;
       &lt;key&gt;&lt;int&gt;1&lt;/int&gt;&lt;/key&gt;
       &lt;value&gt;&lt;string&gt;Audi&lt;/string&gt;&lt;/value&gt;
     &lt;/item&gt;
     &lt;item&gt;
       &lt;key&gt;&lt;int&gt;2&lt;/int&gt;&lt;/key&gt;
       &lt;value&gt;&lt;string&gt;Red&lt;/string&gt;&lt;/value&gt;
     &lt;/item&gt;
   &lt;/dictionary&gt;
  </code></pre>
  
  <p>How do I select all rows of my table with 3 columns: id, name and 1 column that is the content of the <code>string</code> tag where the value of the <code>int</code> tag is equal 1.</p>
  
  <p>The closest to the result I managed to get is:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Problem is, if xml doesn't have a <code>int</code> tag = 1 the row is not returned at all (I would still like to get a row with id, name and <code>NULL</code> in the 3rd column)</p>