SQL Server: FOR XML clause

The FOR XML clause specifies that the result of the SELECT statement should be returned in XML format. DBA/Developer can specify one of the following modes with the FOR XML clause:
RAW: A single <row> element is generated for each row in the rowset. Each non-null column value generates an attribute with the name identical to the column's name or the column's alias.
AUTO: Nested elements are returned using the columns specified in the SELECT list. Each non-null column value generates an attribute that is named according to the column name or column alias. The element nesting is based on the order in which the columns are specified in the SELECT list.
EXPLICIT: Each row in the rowset can be defined in detail by specifying attributes and elements.
PATH: Each row in the rowset can be defined in detail, but column names and column aliases are specified as XPath expressions.
In this scenario, DBA/Developer wanted to retrieve an element for each row that contains an attribute for each referenced column and include a custom root element. When DBA/Developer use AUTO mode, the elements and attributes are generated based on their order in the SELECT list, and the attribute names will be created using column aliases. Specifying the ROOT option will add a root element with the given name. The following query could be used to generate the correctly formatted XML:
 
SELECT ProductID
AS ID, Name, ProductNumber
AS ProdNum
FROM ProductDetails
FOR XML AUTO, ROOT('ProdData');

DBA/Developer should not use FOR XML RAW, ELEMENTS, ROOT('ProdData'). The RAW mode generates a single <row> element for each row. The ELEMENTS option indicates that DBA/Developer want to return columns in the SELECT list as subelements, rather than as attributes. The ROOT option adds a root element with the specified name. Using this clause in DBA/Developer query would produce output in the following format:

 
<ProdData> <row> <ID>1</ID><Name>Adjustable Race</Name><ProdNum>AR-
5381</ProdNum></row><row><ID>2</ID><Name>Bearing Ball</Name><ProdNum>BA-
8327</ProdNum></row><row> <ID>3</ ID><Name>BB Ball
Bearing</Name><ProdNum>BE-2349</ProdNum></row></ProdData>

DBA/Developer should not use FOR XML AUTO, ELEMENTS, ROOT ('ProdData'). Using this clause in DBA/Developer query would produce XML output in the following format:

<ProdData> <ProductDetails> <ID>1</ID><Name>Adjustable

Race</Name><ProdNum>AR-5381</ProdNum></ProductDetails><ProductDetails>
<ID>2</ID><Name>Bearing Ball</ Name><ProdNum>BA-
8327</ProdNum></ProductDetails><ProductDetails> <ID>3</ ID><Name>BB Ball
Bearing</Name><ProdNum>BE-2349</ProdNum></ProductDetails></ ProdData>

DBA/Developer should not use FOR XML PATH(''), ROOT('ProdData'). PATH mode allows DBA/Developer to define in detail the attributes and elements that should be generated in the XML output. The ROOT option will generate a root element with the specified name. Specifying PATH('') will cause all subsequent elements to be subelements of the root element. Using this clause in DBA/Developer query would produce output in the following format:

<ProdData> <ID>1</ID><Name>Adjustable Race</Name><ProdNum>AR-
5381</ProdNum><ID>4</ ID><Name>Headset Ball Bearings</Name><ProdNum>BE-
2908</ProdNum></ProdData>