When using FOR XML EXPLICIT
in SQL Server, we can use the ELEMENT
directive to include a column as an element instead of an attribute. However, this directive doesn’t allow for NULL values. What I mean is that if a column contains a NULL value, no element is produced for that column in the resulting XML document. This may or may not be what we want, depending on the requirements.
If we want such columns to be included in the XML even when they contain NULL values, we can use the ELEMENTXSINIL
directive instead of ELEMENT
.
Example
Suppose we run the following query:
SELECT
PetId,
PetName,
DOB
FROM Pets
ORDER BY PetId, PetName;
Result:
PetId PetName DOB ----- ------- ---------- 1 Fluffy 2020-11-20 2 Fetch null 3 Scratch 2018-10-01
We can see that the DOB
column for Fetch is null
.
Here’s what happens when we use FOR XML EXPLICIT
with the ELEMENT
directive:
SELECT
1 AS Tag,
NULL AS Parent,
PetId AS [Pet!1!PetId],
NULL AS [Details!2!PetName!ELEMENT],
NULL AS [Details!2!DOB!ELEMENT]
FROM Pets
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
PetId,
PetName,
DOB
FROM Pets
ORDER BY [Pet!1!PetId], [Details!2!PetName!ELEMENT]
FOR XML EXPLICIT;
Result:
<Pet PetId="1"> <Details> <PetName> Fluffy </PetName> <DOB> 2020-11-20 </DOB> </Details> </Pet> <Pet PetId="2"> <Details> <PetName> Fetch </PetName> </Details> </Pet> <Pet PetId="3"> <Details> <PetName> Scratch </PetName> <DOB> 2018-10-01 </DOB> </Details> </Pet>
We can see that there’s no DOB
element for Fetch. That’s how the ELEMENT
directive works.
If we want the element to be included even when its value is null
, we can use ELEMENTXSINIL
instead:
SELECT
1 AS Tag,
NULL AS Parent,
PetId AS [Pet!1!PetId],
NULL AS [Details!2!PetName!ELEMENT],
NULL AS [Details!2!DOB!ELEMENTXSINIL]
FROM Pets
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
PetId,
PetName,
DOB
FROM Pets
ORDER BY [Pet!1!PetId], [Details!2!PetName!ELEMENT]
FOR XML EXPLICIT;
Result:
<Pet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" PetId="1"> <Details> <PetName> Fluffy </PetName> <DOB> 2020-11-20 </DOB> </Details> </Pet> <Pet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" PetId="2"> <Details> <PetName> Fetch </PetName> <DOB xsi:nil="true" /> </Details> </Pet> <Pet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" PetId="3"> <Details> <PetName> Scratch </PetName> <DOB> 2018-10-01 </DOB> </Details> </Pet>
This time we got an empty tag for Fetch’s date of birth.
Other Modes
When using other FOR XML
modes, we don’t use the above method to generate elements. We use the ELEMENTS
directive at the end of the query. And if we want to include NULL columns we add the XSINIL
option.
See How to Include Elements that Contain NULL Values When Using FOR XML in SQL Server for more information and examples.