Microsoft® SQL Server 2012 Unleashed

AUTO Mode

When RAW mode is not enough, FOR XML AUTO provides a few more ways to shape your XML output. Its usefulness derives from its capability to produce nested XML elements from rows derived by joining multiple tables, in contrast to the flat structure of RAW mode.

The ROOT keyword introduced earlier also applies with AUTO mode, and it is good practice to continue to use it in your queries. Like RAW mode, AUTO mode produces attribute-centric XML by default, but you can change this by using the ELEMENTS keyword. XSINIL and XMLSCHEMA are also applicable here, having the same effect as with RAW mode. Listing 49.6 illustrates these points.

LISTING 49.6 A SELECT Statement That Uses FOR XML AUTO, ELEMENTS XSINIL, ROOT

Click here to view code image ...