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.