sql - 如何将 NULL 插入 SQL Server DATE 字段 *来自 XML*

Question

我有一些 XML，我正试图使用​​他们的 XML datatype functions 插入到 Microsoft SQL Server 数据库中。 .

其中一个表字段可以为空 DATE柱子。如果该节点丢失，则将其插入为 NULL这很棒。但是，如果节点存在但为空 <LastDay/>运行 XPath 查询时，它将空节点的值解释为 empty string '' instead of 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

Answer 1

您必须使用 NULLIF 函数来避免从 XML 选择中弹出默认值。

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

您的查询将更改如下:

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的更多信息，请查看this MSDN page .