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
?