How to Handle Null Values in XML

Hello Folks,

You might be wondering about how to handle NULLS if it’s a part of XML result set.

Well, I have some keynotes about it, so please see it carefully:

  • Till now, the default implementation is simply to remove the attribute, if the value is NULL.
  • Although for most of the applications, it didn’t make much difference, but for some application it does.
  • NIL in XML nearly equal to NULL in database.
  • Will explain you this by an example:

Here is the SELECT statement, which will give me an output in XML form;

SELECT S.S_Id, S.Name, S.Science_Stream
FROM dbo.[Student] S
FOR XML RAW('Student'), ROOT('Students');

The result set for this is:

1_SQL_Server_How_NULLS_are_handled_in_XML

If you will notice here, the column “Science_Stream” didn’t came for the rest 2 rows, since its NULL for it.

We can also try by adding ELEMENTS directive to make each column an XML element instead of an attribute. So here is the query;

SELECT S.S_Id, S.Name, S.Science_Stream
FROM dbo.Student S
FOR XML RAW('Student'), ROOT('Students'), ELEMENTS;

The result set for this is:

2_SQL_Server_How_NULLS_are_handled_in_XML

   

You can see above that the NULL attributes are not being shown yet.

So, finally you need to add XSINIL directive to the ELEMENTS directive, so that SQL Server can handle NULL by keeping NIL attribute to True. Here’s the select query for it;

SELECT S.S_Id, S.Name, S.Science_Stream
FROM dbo.Student S
FOR XML RAW('Student'), ROOT('Students'), ELEMENTS XSINIL;

The result for this query is:

3_SQL_Server_How_NULLS_are_handled_in_XML

The references http://www.w3.org/2001/XMLSchema-instance is added because the NIL attribute is defined in this namespace.

Well this was something about NULLS with XML.

Hope you have liked it.

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

   

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

2 Comments on “How to Handle Null Values in XML”

  1. Hi Piyush:

    Thank you for posting a very clear explanation.

    I did wonder why you would want an explicit null or nil value in an xml stream, as opposed to simply no tag where there is no corresponding value. If you were inserting a stream of data as opposed to selecting the value for that field would be null either way, correct?

    I guess an update would be a different case, as “UPDATE … SET (field) = null” is different than not updating the field. And of course, a specification might require such a value for some unknown reason outside your control.

    What might be some other scenarios were an explicit null would be beneficial in an xml stream?

Leave a Reply

Your email address will not be published.