Jeff Puckett II Jeff Puckett II - 3 months ago 18
SQL Question

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

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

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.