Up until now you have talked about how to query XML, either a document or fragment, and return tabular data. The FOR XML clause does the reverse, meaning it takes tabular data and returns XML.

The FOR XML clause is a rowset aggregation function that returns a one-row, one-column result set containing an NVARCHAR(MAX) value. Several directives can be applied to the FOR XML clause, which provides different control and structure over the resulting XML. This section discusses the FOR XML clause and the different directives that can be applied.

Before discussing the modes, you need to understand that the XMLDATA directive to the FOR XML options has been deprecated in SQL Server 2012. In its place you should use XSD generation when using the RAW and AUTO modes.


The AUTO directive is the easiest of the directives to use in which to generate XML output from results specified in the SELECT statement. Although it is certainly the easiest of the directives to use, it doesn't provide a lot of control over the resulting structure of the XML output.

The key to the AUTO directive is in its name, in that it “automatically” names the elements and hierarchies based on table and column names, any aliases used, and joins in the query.

For example, the following code illustrates a simple FOR XML AUTO clause:

SELECT CustomerID, OrderNumber, OrderDate
FROM Orders

The results show that the element name is taken from the table in which the data comes from.

<Orders ...

Get Microsoft SQL Server 2012 Bible now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.