SQL Server FOR XML EXPLICIT Examples

In SQL Server, the FOR XML clause allows us to return the results of a query as an XML document. Simply by placing the FOR XML clause at the end of the query will output the results in XML.

When we do this, we have the option of specifying RAW, AUTO, EXPLICIT, or PATH mode. These modes allow us to shape the resulting XML in different ways, and so the mode we choose will determine how the XML is generated.

Below are examples of using EXPLICIT mode when generating XML from a SQL query.

The Original Query

First, here’s the original query without the FOR XML clause:

SELECT 
    p.PetId,
    p.PetName,
    pt.PetType
FROM Pets p 
    INNER JOIN PetTypes pt ON p.PetTypeId = pt.PetTypeId
ORDER BY p.PetId, p.PetName;

Result:

PetId  PetName  PetType
-----  -------  -------
1      Fluffy   Cat    
2      Fetch    Dog    
3      Scratch  Cat

That’s what the data looks like when it’s in a table. The following examples turn that into XML documents.

Basic `FOR XML EXPLICIT` Example

The EXPLICIT option of the FOR XML clause is a lot more complex than the other options. The EXPLICIT option requires that we are very specific with our query to ensure that the resulting XML document is shaped exactly how we want it.

With the other options, we can simply append FOR XML (option) to the query, and the results will be output as XML. Sure, there are other things we can do to shape the XML, but it’s generally quite trivial to get a basic XML document from the query results. But using the EXPLICIT option is a lot more involved. It involves modifying our query specifically for the required output.

Here’s an example of modifying the above query to use FOR XML EXPLICIT:

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;

As mentioned, it’s a lot more involved than just adding a line of code to the end of the query like with the other options.

When I run that with my command line tool, the XML is returned as one long string:

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

The same happens when I run it in my GUI (Azure Data Studio). The XML is returned in one column and one row. However, when running it in my GUI, clicking on the XML in the column opens a new tab with the XML formatted like this:

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

That format was explicitly specified by the way we formulated our query.

The Universal Table

If we remove the last line (i.e. the FOR XML EXPLICIT) from our query, we get the universal table:

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];

Result:

Tag  Parent  Pet!1!PetId  Details!2!PetName  Details!2!PetType
---  ------  -----------  -----------------  -----------------
1    null    1            null               null             
2    1       1            Fluffy             Cat              
1    null    2            null               null             
2    1       2            Fetch              Dog              
1    null    3            null               null             
2    1       3            Scratch            Cat

The ORDER BY clause is important when using the EXPLICIT option. This sorts the results of our universal table so that each child ends up with the correct parent.

The naming convention of the columns determines the element names and the attribute names. For example, Pet!1!PetId tells us that Pet is the element name and PetId is the attribute for that element.

Each Tag value corresponds to one of the columns. Tags with a value of 1 correspond to the column that has !1! in its name (in this case the Pet!1!PetId column), while tags with a value of 2 correspond to the columns that have !2! in their names (Details!2!PetName and Details!2!PetType).

We can see which columns are the top-level elements. The top-level elements are the ones with null in the Parent column. The other ones identify 1 as the Parent. Each of these elements are added as a child to the previous <Pet> element. That’s why the ORDER BY clause is important. If we don’t order our results, we could end up with the wrong XML document.

Change Attributes to Elements

We can use the ELEMENT directive to change an attribute into an element. When we use this directive, we need to apply it against the columns we want to be elements.

Example:

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>

Here, 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.

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.

More Information

See the Microsoft documentation for more examples and information on using EXPLICIT mode with FOR XML.

The Original Query

Basic FOR XML EXPLICIT Example

The Universal Table

Change Attributes to Elements

Dealing with NULL Values

More Information

Basic `FOR XML EXPLICIT` Example