How to parse xml in sql server to process NULL value in DateTime DataType
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
(
F开发者_Go百科K_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.
An XML element can be set to null
like:
<ADDED_DATE xsi:nil="true"/>
I can't find a way to set an attribute to null
though. Perhaps the only way is to omit it?
精彩评论