How to convert SQL Server XML value (xsi: nil) DateTime to null

Question

Is there a way to query the SQL Server XML type so that for an element with xsi:nil="true" , null is returned instead of the default datetime, which is 1900-01-01 00:00:00.000 ?

Here is a snippet of code

 declare @data xml set @data = '<DOD xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />' select Value1 = @data.value('/DOD[1]', 'datetime'), Value2 = IsNull(@data.value('/DOD[1]', 'datetime'), 'NOT NULL?'), Value3 = nullif(@data.value('/DOD[1]', 'datetime'), '1900-01-01') 

Values ​​Value1 and Value2 return 1900-01-01 00:00:00.000 . Is there any way to return zero? without using nullif ?

Answer 1

"datetime" by default is triggered by the start of an empty string, which is equal to "zero", which gives 01 jan 1900.

So: arrange the string, then CAST

 declare @data xml set @data = '<DOD xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />' select Value1 = CAST(NULLIF(@data.value('/DOD[1]', 'varchar(30)'), '') AS datetime) 

Answer 2

easy:

 declare @data xml set @data = '<DOD xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />' select Value1 = @data.value('(/DOD/text())[1]', 'varchar(30)') 

Answer 3

You can also explicitly specify zero like this:

 declare @data xml set @data = '<DOD xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />' select Value1 = @data.value('(/DOD[not(@xsi:nil eq "true")])[1]', 'datetime') 

Answer 4

This case may not be for everyone.

The smart way to do this is to remove the DOD node from XML. So when ever a node you want to be null in your result set, just remove that node when adding it to the XML.

So below will also be null:

 declare @data xml set @data = '<Test> </Test>' select Value1 = @data.value('(/DOD/text())[1]', 'varchar(30)') 

The above example shows that there is no DOD node, therefore, this will result in a null value

Answer 5

I know that this thread has long been dead, but I think this method is a bit simpler:

 SELECT value1 = NULLIF(@data.value('/DOD[1]', 'datetime'), '') 

The string value from the xml string is first evaluated, and if it is empty, it becomes zero date-time.