MSSQL - Handling NULL Values in XML using XQUERY

http://www.sqlservercentral.com/articles/NULL/71331/

declare @xml xml;

select @xml = (

 select

 null as [value]

 for xml path('field'), type, elements xsinil

 );

select @xml;

declare @xml xml

select @xml = N'

<field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 <value xsi:nil="true" />

</field>';

with xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as xsi)

select

 @xml.value('/field[1]/value[1][not(@xsi:nil = "true")]', 'datetime')