In SQL Server, when we use FOR XML to return query results in an XML document, the resulting XML document is returned as a string by default. However, we can change this so that it’s returned in the xml data type.

To do this, all we need to do is append our query with the TYPE directive.

Example

Here’s an example to demonstrate:

SELECT * FROM Pets
FOR XML AUTO, ROOT, TYPE;

Result:

<root>
	<Pets PetId="1" PetTypeId="2" OwnerId="3" PetName="Fluffy" DOB="2020-11-20" />
	<Pets PetId="2" PetTypeId="3" OwnerId="3" PetName="Fetch" DOB="2023-01-15" />
	<Pets PetId="3" PetTypeId="2" OwnerId="2" PetName="Scratch" DOB="2018-10-01" />
</root>

We can’t tell what data type this came back as just by looking at it. But we can use the sys.dm_exec_describe_first_result_set() function to find out:

SELECT system_type_name
FROM sys.dm_exec_describe_first_result_set(
    'SELECT * FROM Pets
FOR XML AUTO, ROOT, TYPE;', 
    null, 
    0);

Result:

xml

It’s xml as expected.

Without the TYPE Directive

Here’s what happens when we remove the TYPE directive from the original query:

SELECT * FROM Pets
FOR XML AUTO, ROOT;

Result:

<root>
	<Pets PetId="1" PetTypeId="2" OwnerId="3" PetName="Fluffy" DOB="2020-11-20" />
	<Pets PetId="2" PetTypeId="3" OwnerId="3" PetName="Fetch" DOB="2023-01-15" />
	<Pets PetId="3" PetTypeId="2" OwnerId="2" PetName="Scratch" DOB="2018-10-01" />
</root>

It looks exactly the same as the first example above. But let’s find out what data type it’s using:

SELECT system_type_name
FROM sys.dm_exec_describe_first_result_set(
    'SELECT * FROM Pets
FOR XML AUTO, ROOT;', 
    null, 
    0);

Result:

ntext

On my system it’s ntext.

However, Microsoft has this to say about the ntext data type:

The ntext, text, and image data types will be removed in a future version of SQL Server.

And this to say about the return data type of FOR XML:

If the TYPE directive is not specified, the FOR XML query result is returned as type nvarchar(max).

Either way, to return FOR XML results as actual xml, all we need to do is use the TYPE directive.

A Word from Microsoft

Microsoft has the following to say with regards to the TYPE directive:

SQL Server returns xml data type instance data to the client as a result of different server-constructs such as FOR XML queries that use the TYPE directive, or where the xml data type is used to return XML instance data values from SQL table columns and output parameters. In client application code, the ADO.NET provider requests this xml data type information to be sent in a binary encoding from the server. However, if you are using FOR XML without the TYPE directive, the XML data comes back as a string type. In any case, the client provider will always be able to handle either form of XML. Note that top-level FOR XML without the TYPE directive cannot be used with cursors.

Source: TYPE directive in FOR XML queries (Microsoft).