FOR XML (SQL Server)

Article
06/13/2017

A SELECT query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in sub queries. The top-level FOR XML clause can be used only in the SELECT statement. In sub queries, FOR XML can be used in the INSERT, UPDATE, and DELETE statements. It can also be used in assignment statements.

In a FOR XML clause, you specify one of these modes:

RAW
AUTO
EXPLICIT
PATH

The RAW mode generates a single <row> element per row in the rowset that is returned by the SELECT statement. You can generate XML hierarchy by writing nested FOR XML queries.

The AUTO mode generates nesting in the resulting XML by using heuristics based on the way the SELECT statement is specified. You have minimal control over the shape of the XML generated. The nested FOR XML queries can be written to generate XML hierarchy beyond the XML shape that is generated by AUTO mode heuristics.

The EXPLICIT mode allows more control over the shape of the XML. You can mix attributes and elements at will in deciding the shape of the XML. It requires a specific format for the resulting rowset that is generated because of query execution. This rowset format is then mapped into XML shape. The power of EXPLICIT mode is to mix attributes and elements at will, create wrappers and nested complex properties, create space-separated values (for example, OrderID attribute may have a list of order ID values), and mixed contents.

However, writing EXPLICIT mode queries can be cumbersome. You can use some of the new FOR XML capabilities, such as writing nested FOR XML RAW/AUTO/PATH mode queries and the TYPE directive, instead of using EXPLICIT mode to generate the hierarchies. The nested FOR XML queries can produce any XML that you can generate by using the EXPLICIT mode. For more information, see Use Nested FOR XML Queries and TYPE Directive in FOR XML Queries.

The PATH mode together with the nested FOR XML query capability provides the flexibility of the EXPLICIT mode in a simpler manner.

These modes are in effect only for the execution of the query for which they are set. They do not affect the results of any subsequent queries.

FOR XML is not valid for any selection that is used with a FOR BROWSE clause.

Example

The following SELECT statement retrieves information from the Sales.Customer and Sales.SalesOrderHeader tables in the AdventureWorks2012 database. This query specifies the AUTO mode in the FOR XML clause:

USE AdventureWorks2012  
GO  
SELECT Cust.CustomerID,   
       OrderHeader.CustomerID,  
       OrderHeader.SalesOrderID,   
       OrderHeader.Status  
FROM Sales.Customer Cust   
INNER JOIN Sales.SalesOrderHeader OrderHeader  
ON Cust.CustomerID = OrderHeader.CustomerID  
FOR XML AUTO

The FOR XML Clause and Server Names

When a SELECT statement with a FOR XML clause specifies a four-part name in the query, the server name is not returned in the resulting XML document when the query is executed on the local computer. However, the server name is returned as the four-part name when the query is executed on a network server.

For example, consider this query:

SELECT TOP 1 LastName  
FROM ServerName.AdventureWorks2012.Person.Person  
FOR XML AUTO

When ServerName is a local server, the query returns the following:

<AdventureWorks2012.Person.Person LastName="Achong" />

When ServerName is a network server, the query returns the following:

<ServerName.AdventureWorks2012.Person.Person LastName="Achong" />

This potential ambiguity can be avoided by specifying this alias:

SELECT TOP 1 LastName  
FROM ServerName.AdventureWorks2012.Person.Person x  
FOR XML AUTO

This query returns the following:

<x LastName="Achong"/>

Share via

FOR XML (SQL Server)

Example

The FOR XML Clause and Server Names

See Also

Additional resources