SQL Server FOR XML AUTO 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 AUTO mode when generating XML from a SQL query.

The Original Query

First, here’s the original 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 in a table. The following examples turn that into XML documents.

Basic `FOR XML AUTO` Example

Here’s what happens when we append FOR XML AUTO to the previous 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 my 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>

The XML has been generated automatically by SQL Server.

The ORDER BY clause is required to ensure all children are grouped under their respective parents.

SQL Server has specific rules around how it generates and formats the XML when the AUTO option is used. Microsoft explains this best:

Each table in the FROM clause, from which at least one column is listed in the SELECT clause, is represented as an XML element. The columns listed in the SELECT clause are mapped to attributes or subelements, if the optional ELEMENTS option is specified in the FOR XML clause.

The XML hierarchy, nesting of the elements, in the resulting XML is based on the order of tables identified by the columns specified in the SELECT clause. Therefore, the order in which column names are specified in the SELECT clause is significant. The first, leftmost table that is identified forms the top element in the resulting XML document. The second leftmost table, identified by columns in the SELECT statement, forms a subelement within the top element, and so on.

If a column name listed in the SELECT clause is from a table that is already identified by a previously specified column in the SELECT clause, the column is added as an attribute of the element already created, instead of opening a new level of hierarchy.

We can see how these rules apply to our example above. For example, we can see that the tables formed XML elements, and their columns were added as attributes.

Nesting the Elements

As stated above, we can use the ELEMENTS option so that the columns form their own elements instead of attributes.

To do this, we can append the above query with , ELEMENTS (note the comma):

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, ELEMENTS;

Result:

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

Aliases

If we create aliases for any tables, the alias is used in the XML output:

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

Result:

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

Grouping and Aggregate Functions

Here’s an example of what happens when we use the GROUP BY clause and an aggregate function such as COUNT():

SELECT
    PetTypes.PetType,
    COUNT( * ) AS TotalNumber
FROM Pets
    INNER JOIN PetTypes ON Pets.PetTypeId = PetTypes.PetTypeId
GROUP BY PetTypes.PetType
ORDER BY PetTypes.PetType
FOR XML AUTO, ELEMENTS;

Result:

<PetTypes>
	<PetType>
		Cat
	</PetType>
	<TotalNumber>
		2
	</TotalNumber>
</PetTypes>
<PetTypes>
	<PetType>
		Dog
	</PetType>
	<TotalNumber>
		1
	</TotalNumber>
</PetTypes>

In this case the resulting XML goes through each pet type and lists how many of that type are in the table.

Computed Columns & Aggregate Columns

When a column in the SELECT clause can’t be associated with any of the tables identified in the FROM clause, as in the case of an aggregate column or computed column, the column is added in the XML document in the deepest nesting level in place at the point that it is encountered in the list.

Example:

SELECT
    Pets.PetId,
    Pets.PetName,
    Pets.DOB,
    PetTypes.PetType,
    CONCAT( Owners.FirstName, Owners.LastName ) AS "OwnerName"
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;

Result:

<Pets PetId="3" PetName="Scratch" DOB="2018-10-01">
	<PetTypes PetType="Cat" OwnerName="BartPitt" />
</Pets>
<Pets PetId="1" PetName="Fluffy" DOB="2020-11-20">
	<PetTypes PetType="Cat" OwnerName="NancySimpson" />
</Pets>
<Pets PetId="2" PetName="Fetch" DOB="2019-08-16">
	<PetTypes PetType="Dog" OwnerName="NancySimpson" />
</Pets>

Here, the computed column was the last item in the SELECT list, and so it was assigned to the innermost element in the XML.

In the following example, the computed column appears as the first column in the SELECT clause, and so it is added to the top element, because the top element is the deepest element encountered at that point:

SELECT
    CONCAT( Owners.FirstName, Owners.LastName ) AS "OwnerName",
    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;

Result:

<Pets PetId="3" PetName="Scratch" DOB="2018-10-01" OwnerName="BartPitt">
	<PetTypes PetType="Cat" />
</Pets>
<Pets PetId="1" PetName="Fluffy" DOB="2020-11-20" OwnerName="NancySimpson">
	<PetTypes PetType="Cat" />
</Pets>
<Pets PetId="2" PetName="Fetch" DOB="2019-08-16" OwnerName="NancySimpson">
	<PetTypes PetType="Dog" />
</Pets>

The same concept applies when using ELEMENTS to output the columns as XML elements instead of attributes. The computed column or aggregate column is assigned to the deepest level element at the point it’s encountered.

The Original Query

Basic FOR XML AUTO Example

Nesting the Elements

Aliases

Grouping and Aggregate Functions

Computed Columns & Aggregate Columns

Basic `FOR XML AUTO` Example