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 RAW
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
That’s what the data looks like when it’s in a table. The following examples turn that into XML documents.
Basic FOR XML RAW
Example
Here’s what happens when we append FOR XML RAW
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 RAW;
When I run that with my command line tool, the XML is returned as one long string:
<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"/>
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" 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" />
The XML has been generated automatically by SQL Server. The above is an example of how SQL Server generates the XML when using the RAW
option. By default, each row is output in a <row>
element, and each column is an attribute of that element.
Change the <row>
Element’s Name
We can change the <row>
element’s name to one of our choosing if we want. To do this, we can append the last row with the name of the element we want, inside parentheses:
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 ( 'Pet' );
Result:
<Pet PetId="1" PetName="Fluffy" DOB="2020-11-20" PetType="Cat" OwnerName="Nancy Simpson" /> <Pet PetId="2" PetName="Fetch" DOB="2019-08-16" PetType="Dog" OwnerName="Nancy Simpson" /> <Pet PetId="3" PetName="Scratch" DOB="2018-10-01" PetType="Cat" OwnerName="Bart Pitt" />
Here I change the element’s name to <Pet>
.
Nested Elements
The above examples have each column listed as attributes to the outer element. We can change this so that each column is its own element. To do this, add the ELEMENTS
option to the last line.
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 ( 'Pet' ), ELEMENTS;
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>
Note that we need to include a comma between ELEMENTS
and the preceding part.
The TYPE
Directive
We can use the TYPE
directive to return the results in the xml data type. This doesn’t affect the contents of the results, just the data type.
To test this, we can use the sys.dm_exec_describe_first_result_set()
function to return the data type of our results.
First, let’s check the data type without the TYPE
directive:
SELECT system_type_name
FROM sys.dm_exec_describe_first_result_set(
'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 ( ''Pet'' ), ELEMENTS;',
null,
0);
Result:
ntext
Without the TYPE
directive, the results are returned as ntext.
Now let’s use the TYPE
directive:
SELECT system_type_name
FROM sys.dm_exec_describe_first_result_set(
'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 ( ''Pet'' ), ELEMENTS, TYPE;',
null,
0);
Result:
xml
This time the results are returned as xml. All I did was add a comma, then TYPE
at the end of the statement.