How to insert NULL into SQL Server DATE field *from XML*

Question

I've got some XML that I'm trying to insert into a Microsoft SQL Server database using their XML datatype functions.

One of the table fields is a nullable DATE column. If the node is missing, then it's inserted as NULL which is great. However, if the node is present but empty <LastDay/> when running the XPath query, it interprets the value from the empty node as an empty string '' instead of NULL. So when looking at the table results, it casts the date to 1900-01-01 by default.

I would like for empty nodes to also be inserted as NULL instead of the default empty string '' or 1900-01-01. How can I get it to insert NULL instead?

CREATE TABLE myxml 
(
    "id" INT,
    "name" NVARCHAR(100),
    "company" NVARCHAR(100),
    "lastday" DATE
);

DECLARE @xml XML =
'<?xml version="1.0" encoding="UTF-8"?>
<Data xmlns="http://example.com" xmlns:dmd="http://example.com/data-metadata">
    <Company dmd:name="Adventure Works Ltd.">
        <Employee id="1">
            <Name>John Doe</Name>
            <LastDay>2016-08-01</LastDay>
        </Employee>
        <Employee id="2">
            <Name>Jane Doe</Name>
        </Employee>
    </Company>
    <Company dmd:name="StackUnderflow">
        <Employee id="3">
            <Name>Jeff Puckett</Name>
            <LastDay/>
        </Employee>
        <Employee id="4">
            <Name>Ill Gates</Name>
        </Employee>
    </Company>
</Data>';    

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)

This produces:

 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

I am trying to achieve:

 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

Answer 1

You have to use NULLIF function to avoid default values popping out from XML selection.

Returns a null value if the two specified expressions are equal.

Your query will be changed as below:

SELECT 
    t.c.value('@id',         'INT' ),
    t.c.value('Name[1]','VARCHAR(100)' ),
    t.c.value('../@dmd:name',    'VARCHAR(100)' ),
    NULLIF(t.c.value('LastDay[1]',  'DATE' ),'')
FROM @xml.nodes('/Data/Company/Employee') t(c)

For more information on NULLIF, please check this MSDN page.

Answer 2

Besides techspider's very good answer I'd like to show another approach:

Doing .nodes() on Company and CROSS APPLY .nodes() on Employee allows a cleaner XPath navigation and avoids the backward navigation you are using by ../@dmd.name. In your case this is just for info probably, but good to consider: If there was a company without any Employee you would skip the whole company otherwise... (My code would skip as well due to the CROSS APPLY, but you could use OUTER APPLY).

And to your actual question: Using the internal cast as xs:date will do the logic within the XQuery and should be faster...

WITH XMLNAMESPACES (DEFAULT 'http://example.com', 'http://example.com/data-metadata' as dmd)
INSERT INTO myxml (id,name,company,lastday)
SELECT 
    e.value('@id',          'INT' ),
    e.value('Name[1]',      'VARCHAR(100)' ),
    c.value('@dmd:name',    'VARCHAR(100)' ),
    e.value('let $x:=LastDay[1] return $x cast as xs:date?','DATE' )
FROM @xml.nodes('/Data/Company') AS A(c) 
CROSS APPLY c.nodes('Employee') AS B(e)