How to parse xml in sql server to process NULL value in DateTime DataType.

Posted by Shantanu Gupta on Stack Overflow See other posts from Stack Overflow or by Shantanu Gupta
Published on 2010-05-14T04:58:45Z Indexed on 2010/05/14 5:04 UTC
Read the original article Hit count: 418

Filed under:
|
|
|
|

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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about Xml