如何在sql server中解析xml以处理DateTime DataType中的NULL值

我在 sql server 中创建了一个示例查询来解析来自 xml 的数据并立即显示它.虽然我会在我的表中插入这些数据，但在此之前我面临一个简单的问题.

我想在日期时间字段 ADDED_DATE="NULL" 中插入 NULL，如下面给出的 xml 所示.但是当我执行这个查询时.它给了我错误

从字符串转换日期时间时转换失败.

我做错了什么.请强调我的错误.

声明@xml varchar(1000)设置@xml='<根目录><TX_MAP FK_GUEST_ID="1" FK_CATEGORY_ID="2" ATTRIBUTE="Test" DESCRIPTION="TestDesc" IS_ACTIVE="1" ADDED_BY="NULL" ADDED_DATE="NULL" MODIFIED_BY="NULL" MODIFIED_DATE="NULL"></TX_MAP><TX_MAP FK_GUEST_ID="2" FK_CATEGORY_ID="1" ATTRIBUTE="Test2" DESCRIPTION="TestDesc2" IS_ACTIVE="1" ADDED_BY="NULL" ADDED_DATE="NULL" MODIFIED_BY="NULL" MODIFIED_DATE="NULL"></TX_MAP>'声明@handle intexec sp_xml_preparedocument @handle 输出，@xmlselect * from OPENXML(@handle,'/ROOT/TX_MAP',1)和(FK_GUEST_ID INT,FK_CATEGORY_ID VARCHAR(10),属性 VARCHAR(100),[描述] VARCHAR(100),IS_ACTIVE VARCHAR(10),ADDED_BY VARCHAR(100),ADDED_DATE 日期时间 NULL,MODIFIED_BY VARCHAR(100),MODIFIED_DATE 日期时间 NULL)

我使用的是 Sql Server 2005.

解决方案

在谷歌搜索了一个小时后，我得到了我的问题的答案，并想与大家分享，以便对未来的用户来说变得容易.

声明@xml varchar(1000)设置@xml='<根目录><TX_MAP FK_GUEST_ID="1" FK_CATEGORY_ID="2" ATTRIBUTE="Test" DESCRIPTION="TestDesc" IS_ACTIVE="1" ADDED_BY="NULL" ADDED_DATE="12/3/2010" MODIFIED_BY="NULL" MODIFIED_DATE="12/3/2010"></TX_MAP><TX_MAP FK_GUEST_ID="2" FK_CATEGORY_ID="1" ATTRIBUTE="Test2" DESCRIPTION="TestDesc2" IS_ACTIVE="1" ></TX_MAP>'声明@handle intexec sp_xml_preparedocument @handle 输出，@xmlselect * from OPENXML(@handle,'/ROOT/TX_MAP',1)和(FK_GUEST_ID INT,FK_CATEGORY_ID VARCHAR(10),属性 VARCHAR(100),[描述] VARCHAR(100),IS_ACTIVE VARCHAR(10),ADDED_BY VARCHAR(100),ADDED_DATE 日期时间,MODIFIED_BY VARCHAR(100),MODIFIED_DATE 日期时间)

<块引用>

你需要做的只是省略将导致的那些属性NULL 值.

I have created a sample query in sql server to parse data from xml and to display it right now. Although I will be inserting this data in my table but before that I am facing a simple problem.

I want to insert NULL in datetime field ADDED_DATE="NULL" as shown in xml given below. But when I executes this query. It gives me error

Conversion failed when converting datetime from character string.

What mistake am i doing. Please highlight my mistake.

declare @xml varchar(1000)
set @xml= '
<ROOT>
    <TX_MAP FK_GUEST_ID="1"  FK_CATEGORY_ID="2" ATTRIBUTE="Test" DESCRIPTION="TestDesc" IS_ACTIVE="1" ADDED_BY="NULL" ADDED_DATE="NULL" MODIFIED_BY="NULL" MODIFIED_DATE="NULL"></TX_MAP>
    <TX_MAP FK_GUEST_ID="2"  FK_CATEGORY_ID="1" ATTRIBUTE="Test2" DESCRIPTION="TestDesc2" IS_ACTIVE="1" ADDED_BY="NULL" ADDED_DATE="NULL" MODIFIED_BY="NULL" MODIFIED_DATE="NULL"></TX_MAP>
</ROOT> '

declare @handle int
exec sp_xml_preparedocument @handle output, @xml

select * from OPENXML(@handle,'/ROOT/TX_MAP',1)
with 
    (
    FK_GUEST_ID INT
    ,FK_CATEGORY_ID VARCHAR(10)
    ,ATTRIBUTE VARCHAR(100)
    ,[DESCRIPTION] VARCHAR(100)
    ,IS_ACTIVE VARCHAR(10)
    ,ADDED_BY VARCHAR(100)
    ,ADDED_DATE DATETIME NULL
    ,MODIFIED_BY VARCHAR(100)
    ,MODIFIED_DATE DATETIME NULL
    )

I am using Sql Server 2005.

解决方案

After googling an hour, I got answer to my question and would like to share with you all so that for future users it become easy.

declare @xml varchar(1000)
set @xml= '
<ROOT>
    <TX_MAP FK_GUEST_ID="1"  FK_CATEGORY_ID="2" ATTRIBUTE="Test" DESCRIPTION="TestDesc" IS_ACTIVE="1" ADDED_BY="NULL" ADDED_DATE="12/3/2010" MODIFIED_BY="NULL" MODIFIED_DATE="12/3/2010"></TX_MAP>
    <TX_MAP FK_GUEST_ID="2"  FK_CATEGORY_ID="1" ATTRIBUTE="Test2" DESCRIPTION="TestDesc2" IS_ACTIVE="1" ></TX_MAP>
</ROOT> '

declare @handle int
exec sp_xml_preparedocument @handle output, @xml

select * from OPENXML(@handle,'/ROOT/TX_MAP',1)
with 
    (
    FK_GUEST_ID INT
    ,FK_CATEGORY_ID VARCHAR(10)
    ,ATTRIBUTE VARCHAR(100)
    ,[DESCRIPTION] VARCHAR(100)
    ,IS_ACTIVE VARCHAR(10)
    ,ADDED_BY VARCHAR(100)
    ,ADDED_DATE DATETIME
    ,MODIFIED_BY VARCHAR(100)
    ,MODIFIED_DATE DATETIME 
    )

What you need to do is just to omit those attributes that will result into NULL value.