You are a database developer on an instance of SQL Server 2008. You have a ProductDetails table defined as follows:
You want retrieve data from the table in the following XML format:
<ProdData>
<ProductDetails ID="1" Name="Adjustable Race" ProdNum="AR-5381" />
<ProductDetails ID="2" Name="Bearing Ball" ProdNum="BA-8327" />
<ProductDetails ID="3" Name="BB Ball Bearing" ProdNum="BE-2349" />
</ProdData>
Which FOR XML clause should you use in your query?
A. FOR XML RAW, ELEMENTS, ROOT(‘ProdData’)
B. FOR XML AUTO, ELEMENTS, ROOT(‘ProdData’)
C. FOR XML AUTO, ROOT(‘ProdData’)
D. FOR XML PATH, ROOT(‘ProdData’)
Correct Answer: C
Explanation/Reference:
The FOR XML clause specifies that the result of the SELECT statement should be returned in XML format. You 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, you wanted to retrieve an element for each row that contains an attribute for each referenced column and include a custom root element. When you 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’);
Another option could be:
SELECT ProductID AS ID, Name, ProductNumber AS ProdNum FROM ProductDetails FOR XML RAW(‘ProductDetails’), ROOT (‘ProdData’);
But there is no such option in the question.
You 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 you 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 your 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>
You should not use FOR XML AUTO, ELEMENTS, ROOT(‘ProdData’). Using this clause in your 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>
You should not use FOR XML PATH(”), ROOT(‘ProdData’). PATH mode allows you 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 your 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>
