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.
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.
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.