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

sql, sql-server, sql-server-2005, t-sql, xml

Solution

The "default" datetime is caused by casting an empty string which is "zero" which gives 01 jan 1900.

So: tidy 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)

Problem

Is there a way to query SQL Server XML type so that for an element with `xsi:nil="true"`, return null instead of default datetime value, which is `1900-01-01 00:00:00.000`? here is a code snippet ``` 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') ``` Value1 & Value2 both returns `1900-01-01 00:00:00.000`. Is there a way to return a null, instead? without using `nullif`?

Original source