Scenario
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)
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
2. Using COALESCE for converting NULL values to defaults
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
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.