SQL Server FOR XML RAW 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 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.

The Original Query

Basic FOR XML RAW Example

Change the <row> Element’s Name

Nested Elements

The TYPE Directive

Basic `FOR XML RAW` Example

Change the `<row>` Element’s Name

The `TYPE` Directive