If you’re getting error msg 6825 that reads “ELEMENTS option is only allowed in RAW, AUTO, and PATH modes of FOR XML“, it’s probably because you’re trying to use the ELEMENTS directive while using EXPLICIT mode of the FOR XML clause.

As the message alludes to, this option is not available when using the EXPLICIT mode (it’s only allowed with the RAW, AUTO, and PATH modes).

However, SQL Server does provide us with an equivalent that provides the same result. When using EXPLICIT mode, we can use the ELEMENT directive instead. We apply this to the column names, instead of the end of the query. We can alternatively use the ELEMENTXSINIL directive to ensure that elements are generated even if the column contains a NULL value.

Example of Error

Here’s an example of code that produces the error:

SELECT
    1 AS Tag,
    NULL AS Parent,
    p.PetId AS [Pet!1!PetId],
    NULL AS [Details!2!PetName],
    NULL AS [Details!2!PetType]
FROM Pets p 
    INNER JOIN PetTypes pt ON p.PetTypeId = pt.PetTypeId
UNION ALL
SELECT
    2 AS Tag,
    1 AS Parent,
    p.PetId,
    p.PetName,
    pt.PetType
FROM Pets p 
    INNER JOIN PetTypes pt ON p.PetTypeId = pt.PetTypeId
ORDER BY [Pet!1!PetId], [Details!2!PetName]
FOR XML EXPLICIT, ELEMENTS;

Result:

Msg 6825, Level 16, State 1, Line 10
ELEMENTS option is only allowed in RAW, AUTO, and PATH modes of FOR XML.

The reason I got the error is because I used the ELEMENTS directive instead of the ELEMENT directive.

Solution

The solution to the above issue is to use the ELEMENT directive instead of the ELEMENTS directive:

SELECT
    1 AS Tag,
    NULL AS Parent,
    p.PetId AS [Pet!1!PetId],
    NULL AS [Details!2!PetName!ELEMENT],
    NULL AS [Details!2!PetType!ELEMENT]
FROM Pets p 
    INNER JOIN PetTypes pt ON p.PetTypeId = pt.PetTypeId
UNION ALL
SELECT
    2 AS Tag,
    1 AS Parent,
    p.PetId,
    p.PetName,
    pt.PetType
FROM Pets p 
    INNER JOIN PetTypes pt ON p.PetTypeId = pt.PetTypeId
ORDER BY [Pet!1!PetId], [Details!2!PetName!ELEMENT]
FOR XML EXPLICIT;

Result:

<Pet PetId="1">
	<Details>
		<PetName>
			Fluffy
		</PetName>
		<PetType>
			Cat
		</PetType>
	</Details>
</Pet>
<Pet PetId="2">
	<Details>
		<PetName>
			Fetch
		</PetName>
		<PetType>
			Dog
		</PetType>
	</Details>
</Pet>
<Pet PetId="3">
	<Details>
		<PetName>
			Scratch
		</PetName>
		<PetType>
			Cat
		</PetType>
	</Details>
</Pet>

When using the ELEMENT directive, we need to add it to the individual columns that we want to be elements. In the above example I added !ELEMENT to two columns, but left the PetId column as an attribute.

Dealing with NULL Values

When using the ELEMENT directive, if a column contains a NULL value, an element is not generated. If we want an element to be generated for NULL values, we can use the ELEMENTXSINIL directive instead of ELEMENT.