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:

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:

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:

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