T-SQL Tips: Representing NULL as Values in XML

This blog explains the various methods that can be applied for representing NULL values while generating a XML document using T-SQL FOR XML statement. Based on your actual requirement you can opt for any of the methods to get the result in your desired format.

Scenario

Consider the below table data

CREATE TABLE OrderDetails
(
OrderID int IDENTITY(1,1),
OrderDesc varchar(1000),
OrderDate datetime,
OrderStatus varchar(100),
ShippedDate datetime,
WebOrder bit
)

INSERT OrderDetails (OrderDesc,OrderDate,OrderStatus,ShippedDate,WebOrder)
VALUES ('Order 1','20150812','Delivered','20150825',NULL),
('Order 2','20151120','Delivered','20151123',NULL),
('Order 3','20151203','Delivered','20151215',1),
('Order 4','20151217','Delivered','20151222',NULL),
('Order 5','20160112','Processing',NULL,NULL),
('Order 6','20160114','Processing',NULL,NULL)

The above table shows details of orders. For simplicity I've included only a subset of columns and 6 indicative rows.
If you analyse the table you can see that there are columns in the table which represent optional fields ie like ShippedDate and WebOrder. They may have NULL values which indicates order has not yet been shipped or its not a web based order.
Now if we try to convert table data to XML using FOR XML PATH we will get the below

SELECT *
FROM OrderDetails
FOR XML PATH('Order'),ROOT('Orders') 

The output will be as shown



If you check the XML you can see that it ignores the node elements that have NULL values in the table for example WebOrder in first two nodes and ShippedDate in last two nodes. This is the default way in which XML gets generated.
If you want NULL values to be respresented in XML you need to follow any of the below approaches

1. Using XSINIL directive

This is the most common method of representing the absence of a value in XML

The query can be written as

SELECT *

FROM OrderDetails

FOR XML PATH('Order'),ROOT('Orders'),ELEMENTS XSINIL

This will cause it to replace NULL instances with xsi nil="true" in the xml

The resultant XML would be as below

As you see it represents NULL value by means of attribute xsi:nil = "true"

2. Using COALESCE for converting NULL values to defaults

In this case we use COALESCE function to convert the NULL values to default blank value.

The code will look like this

SELECT OrderDesc,OrderDate,OrderStatus,COALESCE(CAST(ShippedDate AS varchar(100)),'') AS ShippedDate,COALESCE(CAST(WebOrder AS varchar(100)),'') AS WebOrder

FROM OrderDetails

FOR XML PATH('Order'),ROOT('Orders')

The result would be as below

As you see from the above the missing data will come as blank nodes in this case. This is another way by which you can represent missing value in XML.

3. Using subquery based on FOR XML

Now lets see one more way of representing the missing values. This came as a requirement from the client who insisted they wanted to represent values using self closing nodes (like etc)
This can also be achieved as per below


SELECT OrderDesc,OrderDate,OrderStatus,
(SELECT ShippedDate  AS [*] FOR XML PATH('ShippingDate'),TYPE),
(SELECT WebOrder  AS [*] FOR XML PATH('WebOrder'),TYPE)
FROM OrderDetails
FOR XML PATH('Order'),ROOT('Orders')


Now if you check the result you can see the below

As you see from above it represents missing values by means of self closing nodes which was exactly what client requirement asked for,

Conclusion

From the above methods we can see that there are multiple ways by which NULL values can be represented within XML document
The choice of a particular method depends on your exact requirement
Hope the above post will guide you to adopt a method to fetch you the desired result as per your specific scenario.