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 PATH 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,
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;Result:
PetId PetName DOB PetType OwnerName ----- ------- ---------- ------- ------------- 1 Fluffy 2020-11-20 Cat Nancy Simpson 2 Fetch 2019-08-16 Dog Nancy Simpson 3 Scratch 2018-10-01 Cat Bart Pitt
This is what the data looks like when it’s in a table. The following examples turn that into XML documents.
Basic FOR XML PATH Example
Here’s what happens when we append FOR XML PATH to the previous query:
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;When I run that with my command line tool, the XML is returned as one long string:
<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>
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:
<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>
By default, a <row> element is added for each row in the rowset. We can change that.
Rename the <row> Element
We can rename the <row> element by specifying the name in brackets at the end of the query:
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 ( 'Pet' );Result:
<Pet> <PetId> 1 </PetId> <PetName> Fluffy </PetName> <DOB> 2020-11-20 </DOB> <PetType> Cat </PetType> <OwnerName> Nancy Simpson </OwnerName> </Pet> <Pet> <PetId> 2 </PetId> <PetName> Fetch </PetName> <DOB> 2019-08-16 </DOB> <PetType> Dog </PetType> <OwnerName> Nancy Simpson </OwnerName> </Pet> <Pet> <PetId> 3 </PetId> <PetName> Scratch </PetName> <DOB> 2018-10-01 </DOB> <PetType> Cat </PetType> <OwnerName> Bart Pitt </OwnerName> </Pet>
In this case I renamed the <row> element to <Pet>.
Remove the <row> Element
Alternatively, we can remove the <row> element altogether. To do this, just use an empty string inside the brackets:
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:
<PetId> 1 </PetId> <PetName> Fluffy </PetName> <DOB> 2020-11-20 </DOB> <PetType> Cat </PetType> <OwnerName> Nancy Simpson </OwnerName> <PetId> 2 </PetId> <PetName> Fetch </PetName> <DOB> 2019-08-16 </DOB> <PetType> Dog </PetType> <OwnerName> Nancy Simpson </OwnerName> <PetId> 3 </PetId> <PetName> Scratch </PetName> <DOB> 2018-10-01 </DOB> <PetType> Cat </PetType> <OwnerName> Bart Pitt </OwnerName>
We can see that all elements are still returned, except for the wrapping element.
Create an Attribute
We can create an attribute by creating an alias that starts with @ and doesn’t contain /:
SELECT
p.PetId AS "@id",
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 ( 'Pet' );Result:
<Pet id="1"> <PetName> Fluffy </PetName> <DOB> 2020-11-20 </DOB> <PetType> Cat </PetType> <OwnerName> Nancy Simpson </OwnerName> </Pet> <Pet id="2"> <PetName> Fetch </PetName> <DOB> 2019-08-16 </DOB> <PetType> Dog </PetType> <OwnerName> Nancy Simpson </OwnerName> </Pet> <Pet id="3"> <PetName> Scratch </PetName> <DOB> 2018-10-01 </DOB> <PetType> Cat </PetType> <OwnerName> Bart Pitt </OwnerName> </Pet>
In this case I created an id attribute for the Pet element. The Pet element was defined at the end of the query, and the id attribute was defined when I created the alias for the PetId column.
Add a Root Element
We can add a root element by defining it at the end of the query:
SELECT
p.PetId AS "@id",
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 ( 'Pet' ), ROOT ('Root');Result:
<Root> <Pet id="1"> <PetName> Fluffy </PetName> <DOB> 2020-11-20 </DOB> <PetType> Cat </PetType> <OwnerName> Nancy Simpson </OwnerName> </Pet> <Pet id="2"> <PetName> Fetch </PetName> <DOB> 2019-08-16 </DOB> <PetType> Dog </PetType> <OwnerName> Nancy Simpson </OwnerName> </Pet> <Pet id="3"> <PetName> Scratch </PetName> <DOB> 2018-10-01 </DOB> <PetType> Cat </PetType> <OwnerName> Bart Pitt </OwnerName> </Pet> </Root>
We can provide a name for the root other than Root:
SELECT
p.PetId AS "@id",
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 ( 'Pet' ), ROOT ('Pets');Result:
<Pets> <Pet id="1"> <PetName> Fluffy </PetName> <DOB> 2020-11-20 </DOB> <PetType> Cat </PetType> <OwnerName> Nancy Simpson </OwnerName> </Pet> <Pet id="2"> <PetName> Fetch </PetName> <DOB> 2019-08-16 </DOB> <PetType> Dog </PetType> <OwnerName> Nancy Simpson </OwnerName> </Pet> <Pet id="3"> <PetName> Scratch </PetName> <DOB> 2018-10-01 </DOB> <PetType> Cat </PetType> <OwnerName> Bart Pitt </OwnerName> </Pet> </Pets>
Add a Hierarchy
We can create an alias with a “parent/child” pattern to create a hierarchy within the XML document:
SELECT
p.PetId AS "@id",
p.PetName AS "Details/Name",
p.DOB AS "Details/DOB",
pt.PetType AS "Details/Type",
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 ( 'Pet' ), root ('Root');Result:
<Root> <Pet id="1"> <Details> <Name> Fluffy </Name> <DOB> 2020-11-20 </DOB> <Type> Cat </Type> </Details> <OwnerName> Nancy Simpson </OwnerName> </Pet> <Pet id="2"> <Details> <Name> Fetch </Name> <DOB> 2019-08-16 </DOB> <Type> Dog </Type> </Details> <OwnerName> Nancy Simpson </OwnerName> </Pet> <Pet id="3"> <Details> <Name> Scratch </Name> <DOB> 2018-10-01 </DOB> <Type> Cat </Type> </Details> <OwnerName> Bart Pitt </OwnerName> </Pet> </Root>