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

Question

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?

Answer 1

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)