title: Better way in TSQL to search xml for a node that doesn't exist
body: <p>We have a source <code>XML</code> file that has an <code>address</code> node, and each node is supposed to have a <code>zip_code</code> node beneath in order to validate.  We received a file that failed the schema validation because at least one node was missing it's zip_code (there were several thousand addresses in the file).</p>
  
  <p>We need to find the elements that do not have a zip code, so we can repair the file and send an audit report to the source.</p>
  
  <pre><code>--declare @x xml = bulkcolumn from openrowset(bulk 'x:\file.xml',single_blob) as s
  declare @x xml = N'&lt;addresses&gt;
      &lt;address&gt;&lt;external_address_id&gt;1&lt;/external_address_id&gt;&lt;zip_code&gt;53207&lt;/zip_code&gt;&lt;/address&gt;
      &lt;address&gt;&lt;external_address_id&gt;2&lt;/external_address_id&gt;&lt;/address&gt;
  &lt;/addresses&gt;'
  
  declare @t xml = (
  select @x.query('for $a in .//address 
      return 
          if ($a/zip_code) 
              then &lt;external_address_id /&gt; 
          else $a/external_address_id')
  )
  select x.AddressID.value('.', 'int') AddressID
  from @t.nodes('./external_address_id') x(AddressID)
  where x.AddressID.value('.', 'int') &gt; 0
  GO
  </code></pre>
  
  <p>Really, it's the <code>where</code> clause that bugs me.  I feel like I'm depending on a cast for a <code>null</code> value to <code>0</code>, and it works, but I'm not really sure that it should.  I tried a few variations with the <code>.exist</code> function, but I couldn't get the correct result.</p>