title: Treating empty elements as nulls in SQL Server's OPENXML function
body: <p>I have the following (highly simplified) XML document that I am reading into my database using the OPENXML function:</p>
  
  <pre><code>&lt;root&gt;
      &lt;row&gt;
          &lt;bar&gt;123&lt;/bar&gt;
      &lt;/row&gt;
      &lt;row&gt;
          &lt;bar&gt;0&lt;/bar&gt;
      &lt;/row&gt;
      &lt;row&gt;
          &lt;bar&gt;&lt;/bar&gt;
      &lt;/row&gt;
  &lt;/root&gt;
  </code></pre>
  
  <p>I am importing in into the database like so:</p>
  
  <pre><code>insert into [Foo]
      ([bar])
  select
      ds.[bar]
  from openxml(@xmlHandle, 'root/row', 2)
  with ([bar] int) ds
  </code></pre>
  
  <p>The problem is that OPENXML converts empty fields with the int data type to zero, so this gets inserted into my table:</p>
  
  <pre><code>bar
  ----
  123
  0
  0
  </code></pre>
  
  <p>What I want inserted into my table is:</p>
  
  <pre><code>bar
  ----
  123
  0
  NULL
  </code></pre>
  
  <p>How do I get the OPENXML function to treat empty fields as NULL and not convert it to zero by default?</p>