When using FOR XML in SQL Server, we can use the ELEMENTS directive to include a column as an element instead of an attribute. However by default, 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, all we need to do is include the XSINIL option. This option specifies that any column that has a NULL value automatically gets an element with xsi:nil="true" in the resulting XML.

The alternative is ABSENT, which means columns with NULL values are excluded (this is the default behaviour).

Example

Suppose we run the following query:

SELECT
    PetId,
    DOB
FROM Pets
ORDER BY PetId;

Result:

PetId  DOB                     
-----  ----------
1      2020-11-20
2      null                    
3      2018-10-01

We can see that the DOB column for the second pet is null.

Here’s what happens when we run FOR XML AUTO, ELEMENTS with the XSINIL option:

SELECT
    PetId,
    DOB
FROM Pets
ORDER BY PetId
FOR XML AUTO, ELEMENTS XSINIL;

Result:

<Pets xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<PetId>
		1
	</PetId>
	<DOB>
		2020-11-20
	</DOB>
</Pets>
<Pets xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<PetId>
		2
	</PetId>
	<DOB xsi:nil="true" />
</Pets>
<Pets xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<PetId>
		3
	</PetId>
	<DOB>
		2018-10-01
	</DOB>
</Pets>

We can see that there’s an empty DOB element for the second pet, even though that column contains a NULL value.

Here’s what happens when we run it without the XSINIL option:

SELECT
    PetId,
    DOB
FROM Pets
ORDER BY PetId
FOR XML AUTO, ELEMENTS;

Result:

<Pets>
	<PetId>
		1
	</PetId>
	<DOB>
		2020-11-20
	</DOB>
</Pets>
<Pets>
	<PetId>
		2
	</PetId>
</Pets>
<Pets>
	<PetId>
		3
	</PetId>
	<DOB>
		2018-10-01
	</DOB>
</Pets>

We can see that there’s no DOB element for the second pet. This is the default behaviour.

We can get the same result by explicitly including the ABSENT option:

SELECT
    PetId,
    DOB
FROM Pets
ORDER BY PetId
FOR XML AUTO, ELEMENTS ABSENT;

Result:

<Pets>
	<PetId>
		1
	</PetId>
	<DOB>
		2020-11-20
	</DOB>
</Pets>
<Pets>
	<PetId>
		2
	</PetId>
</Pets>
<Pets>
	<PetId>
		3
	</PetId>
	<DOB>
		2018-10-01
	</DOB>
</Pets>

Once again, there’s no DOB element for the second pet.

EXPLICIT Mode

If you’re using EXPLICIT, the syntax is a bit different. In EXPLICIT mode, we use the ELEMENT (without the S) directive against each column that we want to be returned as an element. If we want NULL columns to appear, we use ELEMENTXSINIL instead of ELEMENT.