The ELEMENTS option of the FOR XML AUTO clause causes AUTO mode to return nested elements instead of attributes. Depending on your business needs or those of your clients, element-centric mapping may be preferable to the default attribute-centric mapping. Here's an example of a FOR XML query that returns elements instead of attributes:
SELECT CustomerID, CompanyName FROM Customers FOR XML AUTO, ELEMENTS
(Results abridged and formatted)
XML_F52E2B61-18A1-11d1-B105-00805F49916B ------------------------------------------------------ <Customers> <CustomerID>ALFKI</CustomerID> <CompanyName>Alfreds Futterkiste</CompanyName> </Customers> <Customers> <CustomerID>ANATR</CustomerID> <CompanyName>Ana Trujillo Emparedados y helados</CompanyName> </Customers> <Customers> <CustomerID>ANTON</CustomerID> <CompanyName>Antonio Moreno Taquería</CompanyName> </Customers> <Customers> <CustomerID>AROUT</CustomerID> <CompanyName>Around the Horn</CompanyName> </Customers> <Customers> <CustomerID>WILMK</CustomerID> <CompanyName>Wilman Kala</CompanyName> </Customers> <Customers> <CustomerID>WOLZA</CustomerID> <CompanyName>Wolski Zajazd</CompanyName> </Customers>
Notice that the ELEMENTS option has caused what were being returned as attributes of the Customers element to instead be returned as subelements. Each attribute is now a pair of element tags that enclose the value from a column in the table.
NOTE
Currently, AUTO mode does not support GROUP BY or aggregate functions. The heuristics it uses to determine element names are incompatible with these constructs, so you cannot use them in AUTO mode queries. Additionally, FOR XML itself is incompatible with COMPUTE, so you can't use it in FOR XML queries of any kind.