SQL NULL values in XML – XSINIL

This is a series post on XML. In my previous post I have discussed about XML modes in SQL Server. If you are new to XML then I would recommend you to read the previous post first.

You must be wondering, how to handle NULL values while streaming XML from SQL server using FOR XML clause, because NULL values does not get included into the XML document by default and hence the particular element directive gets eliminated from the XML. Answer to this question is simple, we can include the NULL element into the XML using XSINIL

SAMPLE SCRIPT

XSINIL_01

 

In above books table, we have a NULL value in “Price” column for id “bk105” so let’s stream the XML for id “105” and at the same time will stream for id “106” just to get a full comparison between NULL and NOT NULL behaviors while streaming a XML document.

XSINIL_02

Now let’s use XSINIL in the same query.

XSINIL_03We got the price element back in XML decoument. xsi:nil

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply