如何从XML *向SQL Server DATE字段*中插入NULL(How to insert NULL into SQL Server DATE field *from XML*)

我有一些XML，我试图使用他们的XML数据类型函数插入到Microsoft SQL Server数据库中。

其中一个表字段是可以为空的DATE列。 如果节点丢失，则将其插入为NULL ，这很好。 但是，如果节点存在但在运行XPath查询时为空<LastDay/> ，则它将空节点中的值解释为空字符串''而不是NULL 。 因此，在查看表结果时，默认情况下会将日期转换为1900-01-01。

我想将空节点也插入为NULL而不是默认的空字符串''或1900-01-01。 我怎样才能让它插入NULL呢？

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)
 
 这会产生： 
 
 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
 
 我想要实现： 
 
 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
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

                
最满意答案
                
                    
                        
 您必须使用NULLIF函数来避免从XML选择中弹出默认值。 
 
 
 
 如果两个指定的表达式相等，则返回null值。 
 
 
 您的查询将更改如下： 
 
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)
 
 有关NULLIF更多信息，请查看此MSDN页面 。 
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.