You are a database developer on an instance of SQ`n the exhibit. (Click the Exhibit(s) button.)
You want to retrieve data from the Prospect table in XML format. You want the XML in the result set to meet the following requirements:
• A root <ProspectData> element should enclose the XML.
• Each prospect should be represented with a <Prospect> element that includes an ID and Name attribute.
• Each <Prospect> element should enclose a <Detail> element that includes subelements for the type and rating.
You want to accomplish this with the least amount of effort.
Which query should you execute?
Exhibit:
A. SELECT ID AS "@ID",
CompanyName AS "@Name",
Type AS "Detail/Type",
Rating AS "Detail/Rating"
FROM Prospect
FOR XML PATH(‘Prospect’), Root(‘ProspectData’);
B. SELECT AS Tag,
NULL AS Parent,
ID AS [Prospect!1!ID!element],
CompanyName AS [Prospect!1!Name],
Type AS [Prospect!1!Type],
NULL AS [Prospect!1!Rating]
FROM Prospect
FOR XML EXPLICIT ROOT(‘ProspectData’);
C. SELECT ID,
Type AS Type,
Rating AS Rating
FROM Prospect
FOR XML AUTO, TYPE, ROOT(‘ProspectData’);
D. SELECT ID AS "@ID",
Type AS "Type",
Rating AS "Rating"
INTO XMLOutputTbl
FROM Prospect
FOR XML PATH(‘Prospect’), Root(‘ProspectData’);
Correct Answer: A
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 be 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 named according to the column name or column alias. The element nesting is based on the order the columns are specified in the SELECT list.
EXPLICIT: Each row in the rowset can be defined in detail 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.
When using PATH mode with the FOR XML clause, you can define each row in the rowset in detail. In this scenario, you specified the ROOT option, which will generate a root element named <ProspectData> in the output that will enclose the other elements. You should also specify PATH (‘Prospect’). This will create a <Prospect> element that will enclose the other elements. With FOR XML PATH, you can use aliases in the SELECT list to further control how the XML is formatted. In this scenario, you specified the ID column using the "@ID" alias and the CompanyName column using the "@Name" alias. Because the alias begins with an ampersand (@), this will generate ID and Name attributes for each <Prospect> element. Next, you should specify an alias of "Detail/Type" for the Type column. Because the alias does not begin with an ampersand (@), this generates a <Detail> element within the <Prospect> element that contains a <Type> subelement. Finally, you should specify an alias of "Detail/Rating" for the Rating column, which generates an additional <Rating> subelement within the <Detail> element. Using PATH mode is less complex than using EXPLICIT mode, but provides much more flexibility mapping data to XML than the other modes.
This query would generate XML output similar to the following, with a <Prospect> element for each row returned:
<ProspectData><Prospect ID="1" Name="VirtuArt"><Detail> <Type>A</Type><Rating>9</Rating></Detail></Prospect><Prospect ID="2" Name="NuTex Corporation"><Detail> <Type>C</Type><Rating>3</Rating></Detail></Prospect><Prospect ID="3" Name="InterConn"><Detail> <Type>A</Type><Rating>7</Rating></Detail>
</Prospect></ProspectData>
You should not use the query that specifies the TYPE option in the FOR XML clause because it does not format the XML as required. When you specify the TYPE option, the XML is returned as an xml data type instance rather than XML text. This query would output an xml data type that represented the data in a format similar to the following:
<ProspectData><Prospect ID="1" Name="VirtuArt" Type="A" Rating="9" /><Prospect ID="2" Name="NuTex Corporation" Type="C"
Rating="3" /><Prospect ID="3" Name="InterConn" Type="A" Rating="7" /></ProspectData>
The TYPE option can be very useful in some situations, such as if you wanted to nest queries that used FOR XML, or if you wanted to assign the result to an xml variable to perform some additional processing.
You should not use the query that includes an INTO clause in the SELECT statement because the FOR XML clause cannot be used with query that uses the INTO clause. This query would generate the following error message:
Msg 6819, Level 16, State 1, Line 9The FOR XML clause is not allowed in a SELECT INTO statement.
You should not use the query that uses the FOR XML EXPLICIT clause. With FOR XML EXPLICIT, you can provide details of how the XML result should be formatted, but the syntax is more complex than using FOR XML PATH. The first two columns in the SELECT statement must specify the Tag column and the Parent column, which indicate the level of the current tag and the current tag’s parent element, respectively. The other columns in the SELECT list are actual columns in the table. The output would be similar to the following:
<ProspectData><Prospect Name="VirtuArt" Type="A"><ID>1</ID></Prospect><Prospect Name="NuTex Corporation"
Type="C"><ID>2</ID></Prospect><Prospect Name="InterConn" Type="A"><ID>3</ID></Prospect></ProspectData>
To include nested elements within the output, you would explicitly format each level in a separate SELECT statement, specifying a different Tag value and the tag’s parent, and combine the result sets using the UNION ALL operator.