SQL Server 2005 adds support for Null Values in the "For XML" clause
For those of you who have been using the "For XML" clause in SQL Server 2000, you will be happy to know that support for Null Values has been added to it in SQL Server 2005. This may not seem like a big deal on the surface, though, but it really is. In the past, if you have worked with tables that contained Null values, you will know that elements are ommitted in the results if the value is Null. This can cause difficulties when you are expecting a consistent set of XML elements to be returned as part of your results. Without it, you will have had to go through extra work for those missing fields. You could not simply check the value of it. Instead, you would have to check if the element even exists first. This can be very time consuming and can add checks to your code that can really clutter it up and make it hard to read through. In the past, you would have had to solve the problem by either making sure that your tables did not contain any Null values or adding the Coalesce function all throughout your stored procedures to ensure that any Null values were returned as empty strings.
Now, with SQL Server 2005, these additional checks have become a thing of the past thanks to a new XSINILL directive that you can add to your "For XML" clause. The XSINIL will cause Null values to be returned as empty elements. Although this feature is only one of the minor enhancements to SQL Server, it is among my favorites. To demonstrate how this directive can be used, let's start with a SQL Statement below. One that uses the AdventureWorks database included in SQL Server 2005. If we take a examine the Query below, we will see that it returns the ProductID, Name and Color from the Product table. Simple stuff.
SELECT ProductID, Name, Color
FROM Production.Product Product
FOR XML AUTO, ELEMENTS XSINIL
The results of this query will retun a number of Product elements. However, in cases where the value is Null, such as the one shown below, an empty element is returned. In this example, you can see that the Color element has a Null value in the database and is returned as an empty element
<Color xsi:nil="true" />
If we were using SQL Server 2000, the element would be ommitted as shown below. In fact, that was really our only option. As you may imagine, having to code for conditions where elements exist only part of the time can require some additional and painful checks to your coding. I am happy to see that there is now a way to handle it.
This last example below demonstrates the results of a query where the color field has a value and you have all 3 child elements of the Product element. As you can see, all elements are accountable.
All in all, this new feature allows us to ensure that we have consistent set of XML elements returned when we use the "For XML" clause and to avoid those ragged XML elements that we had to work around in the previous version. In my world, it is a very welcome enhancement that will make my life as a developer just a little bit easier.