If you’re using FOR XML in SQL Server to output your query results as XML, you might find yourself in the situation where the XML returned has no root element. Fortunately adding a root element is easy, as I’ll show you in this article.

The ROOT Argument

To add a root element when using FOR XML, all we need to do is append ROOT ('RootName') to our query, being sure to use a comma to separate this argument with the rest of the query. Simply replace RootName with the name you want to give to the root element.

We can also omit the root name in order to add a root element called <root>.

Example

Here’s an example of adding a root element when running a query with FOR XML AUTO:

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, ROOT ('PetHouse');

Result:

<PetHouse>
	<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="2023-01-15">
			<PetTypes PetType="Dog" />
		</Pets>
	</Owners>
</PetHouse>

Here I added a root called PetHouse. We can simply change the name of the root element by changing PetHouse to something else.

Default Root Name

We can also add a root element without specifying its name. To do this, we can just specify ROOT without anything else:

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

Result:

<root>
	<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="2023-01-15">
			<PetTypes PetType="Dog" />
		</Pets>
	</Owners>
</root>

In other words, naming the root element is optional.

Without the Root Element

Here’s an example of what happens when we don’t add a root element:

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;

Result:

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

This time there’s no root element, and the Owners elements are at the top level.