title: How to insert NULL into SQL Server DATE field *from XML*
body: <p>I've got some XML that I'm trying to insert into a Microsoft SQL Server database using their <a href="https://msdn.microsoft.com/en-us/library/ms190798.aspx" rel="nofollow noreferrer">XML datatype functions</a>.</p>
  
  <p>One of the table fields is a nullable <code>DATE</code> column. If the node is missing, then it's inserted as <code>NULL</code> which is great. However, if the node is present but empty <code>&lt;LastDay/&gt;</code> when running the XPath query, it interprets the value from the empty node as an <a href="https://stackoverflow.com/a/17070904/4233593">empty string <code>''</code> instead of <code>NULL</code></a>. So when looking at the table results, it casts the date to 1900-01-01 by default.</p>
  
  <p>I would like for empty nodes to also be inserted as <code>NULL</code> instead of the default empty string <code>''</code> or 1900-01-01. How can I get it to insert <code>NULL</code> instead?</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE myxml 
  (
      "id" INT,
      "name" NVARCHAR(100),
      "company" NVARCHAR(100),
      "lastday" DATE
  );
  
  DECLARE @xml XML =
  '&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;Data xmlns="http://example.com" xmlns:dmd="http://example.com/data-metadata"&gt;
      &lt;Company dmd:name="Adventure Works Ltd."&gt;
          &lt;Employee id="1"&gt;
              &lt;Name&gt;John Doe&lt;/Name&gt;
              &lt;LastDay&gt;2016-08-01&lt;/LastDay&gt;
          &lt;/Employee&gt;
          &lt;Employee id="2"&gt;
              &lt;Name&gt;Jane Doe&lt;/Name&gt;
          &lt;/Employee&gt;
      &lt;/Company&gt;
      &lt;Company dmd:name="StackUnderflow"&gt;
          &lt;Employee id="3"&gt;
              &lt;Name&gt;Jeff Puckett&lt;/Name&gt;
              &lt;LastDay/&gt;
          &lt;/Employee&gt;
          &lt;Employee id="4"&gt;
              &lt;Name&gt;Ill Gates&lt;/Name&gt;
          &lt;/Employee&gt;
      &lt;/Company&gt;
  &lt;/Data&gt;';    
  
  WITH XMLNAMESPACES (DEFAULT 'http://example.com', 'http://example.com/data-metadata' as dmd)
  INSERT INTO myxml (id,name,company,lastday)
  SELECT 
      t.c.value('@id',         'INT' ),
      t.c.value('Name[1]',     'VARCHAR(100)' ),
      t.c.value('../@dmd:name','VARCHAR(100)' ),
      t.c.value('LastDay[1]',  'DATE' )
  FROM @xml.nodes('/Data/Company/Employee') t(c)
  </code></pre>
  
  <p>This produces:</p>
  
  <pre><code> id  name         company              lastday
   ------------------------------------------------
   1   John Doe     Adventure Works Ltd. 2016-08-01
   2   Jane Doe     Adventure Works Ltd. NULL
   3   Jeff Puckett StackUnderflow       1900-01-01
   4   Ill Gates    StackUnderflow       NULL
  </code></pre>
  
  <p>I am trying to achieve:</p>
  
  <pre><code> id  name         company              lastday
   ------------------------------------------------
   1   John Doe     Adventure Works Ltd. 2016-08-01
   2   Jane Doe     Adventure Works Ltd. NULL
   3   Jeff Puckett StackUnderflow       NULL
   4   Ill Gates    StackUnderflow       NULL
  </code></pre>