Xml with null Column

How to Retrieve Null Values from an XML Column

XML Basics

Some key points to remember when working with XML:

• XML tags are case-sensitive; make sure the opening and closing tags match in case.
• Elements in XML must be correctly nested.
• Attribute values should always be enclosed in quotes.
• The attribute xsi:nil is used to represent a null value in XML.

Special Characters in XML:

<message>salary < 1000</message>
&lt; < less than
&gt; > greater than
&amp; & ampersand 
&apos; ' apostrophe
&quot; " quotation mark

Example SQL Query:

select name, object_id, principal_id from sys.tables for XML PATH, ELEMENTS XSINIL;

Sample XML Output:

object_id	principal_id	name
117575457	NULL	spt_fallback_db
133575514	NULL	spt_fallback_dev
149575571	NULL	spt_fallback_usg
1803153469	NULL	spt_monitor
2107154552	NULL	MSreplication_options

SQL to Parse XML with Null Values:

declare @xml xml
select @xml = N'<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>spt_fallback_db</name>
  <object_id>117575457</object_id>
  <principal_id xsi:nil="true" />
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>spt_fallback_dev</name>
  <object_id>133575514</object_id>
  <principal_id xsi:nil="true" />
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>spt_fallback_usg</name>
  <object_id>149575571</object_id>
  <principal_id xsi:nil="true" />
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>spt_monitor</name>
  <object_id>1803153469</object_id>
  <principal_id xsi:nil="true" />
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>MSreplication_options</name>
  <object_id>2107154552</object_id>
  <principal_id xsi:nil="true" />
</row>'
; 
with xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as xsi)
select  N.C.value('object_id[1]', 'int') as object_id,
        N.C.value('principal_id[1][not(@xsi:nil = "true")]', 'int') as principal_id,
        N.C.value('name[1]', 'varchar(100)') as name
from @xml.nodes('//row') N(C)

For Additional Information:

You can refer to the following article for more details: XML Type Using Query