SQL Server FOR XML Explained

In SQL Server, the FOR XML clause allows us to return the results of a query as an XML document.

When we use this clause, we have various options for how we want to shape the resulting XML document.

Below is an explanation of how the FOR XML clause works, including the basic options we have when using it.

Modes

When using FOR XML, we specify which mode we want to use. The available modes are RAW, AUTO, PATH, and EXPLICIT. These are explained in the following table.

`RAW`	Generates a single `<row>` element per row in the rowset that is returned by the `SELECT` statement. We can also modify our query to create an XML hierarchy.
`AUTO`	Generates nesting in the resulting XML by using heuristics, based on the way the `SELECT` statement is specified. We have minimal control over the shape of the XML generated.
`PATH`	Provides a simple way to shape the XML to our specific needs. Enables us to do many things that `EXPLICIT` mode can do, but with less complex code.
`EXPLICIT`	Provides the most flexibility in generating the XML we want from the query result. However, it requires a lot more complexity in our code than `PATH` (and the other modes).

Sample Query Without the `FOR XML` Clause

First, let’s look at a query without the FOR XML clause:

SELECT
    Owners.FirstName AS "OwnerFirstName",
    Owners.LastName AS "OwnerLastName",
    Pets.PetId,
    Pets.PetName,
    Pets.DOB,
    PetTypes.PetType
FROM Pets
    INNER JOIN PetTypes ON Pets.PetTypeId = PetTypes.PetTypeId
    INNER JOIN Owners ON Pets.OwnerId = Owners.OwnerId
ORDER BY Owners.OwnerId;

Result:

OwnerFirstName  OwnerLastName  PetId  PetName  DOB         PetType
--------------  -------------  -----  -------  ----------  -------
Bart            Pitt           3      Scratch  2018-10-01  Cat    
Nancy           Simpson        1      Fluffy   2020-11-20  Cat    
Nancy           Simpson        2      Fetch    2019-08-16  Dog

This is what the data looks like when it’s returned in tabular format. This is what we’re used to when we run queries in SQL Server (without using FOR XML).

`AUTO` Mode

Now let’s append FOR XML AUTO to the above query:

SELECT
    Owners.FirstName AS "OwnerFirstName",
    Owners.LastName AS "OwnerLastName",
    Pets.PetId,
    Pets.PetName,
    Pets.DOB,
    PetTypes.PetType
FROM Pets
    INNER JOIN PetTypes ON Pets.PetTypeId = PetTypes.PetTypeId
    INNER JOIN Owners ON Pets.OwnerId = Owners.OwnerId
ORDER BY Owners.OwnerId
FOR XML AUTO;

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

<Owners OwnerFirstName="Bart" OwnerLastName="Pitt"><Pets PetId="3" PetName="Scratch" DOB="2018-10-01"><PetTypes PetType="Cat"/></Pets></Owners><Owners OwnerFirstName="Nancy" OwnerLastName="Simpson"><Pets PetId="1" PetName="Fluffy" DOB="2020-11-20"><PetTypes PetType="Cat"/></Pets><Pets PetId="2" PetName="Fetch" DOB="2019-08-16"><PetTypes PetType="Dog"/></Pets></Owners>

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 the GUI, clicking on the XML in the column opens a new tab with the XML formatted like this:

<Owners OwnerFirstName="Bart" OwnerLastName="Pitt">
	<Pets PetId="3" PetName="Scratch" DOB="2018-10-01">
		<PetTypes PetType="Cat" />
	</Pets>
</Owners>
<Owners OwnerFirstName="Nancy" OwnerLastName="Simpson">
	<Pets PetId="1" PetName="Fluffy" DOB="2020-11-20">
		<PetTypes PetType="Cat" />
	</Pets>
	<Pets PetId="2" PetName="Fetch" DOB="2019-08-16">
		<PetTypes PetType="Dog" />
	</Pets>
</Owners>

We can see that SQL Server has returned the result as XML. The XML has been generated automatically by SQL Server, based on AUTO mode.

See SQL Server FOR XML AUTO Examples for more examples of what we can do when using AUTO mode.

`RAW` Mode

Here’s an example of a query that uses RAW mode:

SELECT 
    p.PetId,
    p.PetName,
    p.DOB,
    pt.PetType,
    CONCAT(o.FirstName, ' ', o.LastName) AS OwnerName
FROM Pets p 
    INNER JOIN PetTypes pt ON p.PetTypeId = pt.PetTypeId
    INNER JOIN Owners o ON p.OwnerId = o.OwnerId
FOR XML RAW;

Result:

<row PetId="1" PetName="Fluffy" DOB="2020-11-20" PetType="Cat" OwnerName="Nancy Simpson" />
<row PetId="2" PetName="Fetch" DOB="2019-08-16" PetType="Dog" OwnerName="Nancy Simpson" />
<row PetId="3" PetName="Scratch" DOB="2018-10-01" PetType="Cat" OwnerName="Bart Pitt" />

When we use RAW mode, each row in the result is presented as a separate element called <row>. We can change the name of this element if required.

In RAW mode, the columns are presented as attributes to the <row> element (or whatever name we give it). We can change our query to have the columns presented in their own elements.

See SQL Server FOR XML RAW Examples for more examples that use this mode.

`PATH` Mode

Here’s an example of a query that uses PATH mode:

SELECT
    p.PetId,
    p.PetName,
    p.DOB,
    pt.PetType,
    CONCAT(o.FirstName, ' ', o.LastName) AS OwnerName
FROM Pets p 
    INNER JOIN PetTypes pt ON p.PetTypeId = pt.PetTypeId
    INNER JOIN Owners o ON p.OwnerId = o.OwnerId
FOR XML PATH;

Result:

<row>
	<PetId>
		1
	</PetId>
	<PetName>
		Fluffy
	</PetName>
	<DOB>
		2020-11-20
	</DOB>
	<PetType>
		Cat
	</PetType>
	<OwnerName>
		Nancy Simpson
	</OwnerName>
</row>
<row>
	<PetId>
		2
	</PetId>
	<PetName>
		Fetch
	</PetName>
	<DOB>
		2019-08-16
	</DOB>
	<PetType>
		Dog
	</PetType>
	<OwnerName>
		Nancy Simpson
	</OwnerName>
</row>
<row>
	<PetId>
		3
	</PetId>
	<PetName>
		Scratch
	</PetName>
	<DOB>
		2018-10-01
	</DOB>
	<PetType>
		Cat
	</PetType>
	<OwnerName>
		Bart Pitt
	</OwnerName>
</row>

As with RAW mode, we can change the name of the <row> element to one of our choosing. We can also remove it altogether, add a root element, and more.

By default, the columns are presented as elements in an XML hierarchy. But we can change this if required. We can specify that some, or all, columns are to be presented as attributes.

We can also create our own elements to produce a “parent/child” pattern that interacts with the columns from our query. In other words, we can nest columns from the query inside a newly created element of our choosing.

For more examples, see SQL Server FOR XML PATH Examples.

`EXPLICIT` Mode

Here’s an example of a query that uses EXPLICIT mode:

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;

Result:

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

As mentioned, EXPLICIT mode is a lot more involved than the other options. In most cases we can use PATH mode to achieve the same result, but EXPLICIT mode does allow us to be very specific with how we want our results to be returned.

See SQL Server FOR XML EXPLICIT Examples for more examples, as well as an explanation of how this mode works.

More Information

The FOR XML clause includes a few other options that allow us to customise our XML precisely to our needs. For more information see Microsoft’s documentation on the FOR XML clause and its basic syntax.

Modes

Sample Query Without the FOR XML Clause

AUTO Mode

RAW Mode

PATH Mode

EXPLICIT Mode

More Information

Sample Query Without the `FOR XML` Clause

`AUTO` Mode

`RAW` Mode

`PATH` Mode

`EXPLICIT` Mode