How to parse xml in sql server to handle NULL value in DateTime DataType

Question

I created a sample query in SQL Server to parse data from xml and display it right now. Although I will insert this data into my table, but before that I ran into a simple problem.

I want to insert NULL in the datetime field ADDED_DATE="NULL" , as shown in the xml below. But when I execute this request. It gives me an error

Conversion error while converting date and time from character string.

What a mistake I am making. Please pay attention to 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.

Answer 1

After I set off at one o'clock, I received an answer to my question and would like to share everything with you so that future users can easily.

 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 simply omit those attributes that are NULL .

Answer 2

XML element null can be assigned, for example:

 <ADDED_DATE xsi:nil="true"/> 

I can not find a way to set the attribute on null . Perhaps the only way is to omit it?