Microsoft SQL Server 2012 Bible

FOR XML

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.

Note

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.

Auto

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
FOR XML AUTO

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

<Orders ...