You are a database developer on an instance of SQL Server 2008. You execute the following Transact-SQL:
DECLARE @iHndl int;
DECLARE @XmlDoc nvarchar(4000);
SET @XmlDoc = N’
<Exams>
<Exam ID="70-451" Desc="PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008">
<Obj ObjDesc="Designing a Database Strategy" />
<Obj ObjDesc="Designing Database Tables" />
<Obj ObjDesc="Designing Programming Objects" />
<Obj ObjDesc="Designing a Transaction and Concurrency Strategy" />
<Obj ObjDesc="Designing an XML Strategy" />
<Obj ObjDesc="Designing Queries for Performance" />
<Obj ObjDesc="Designing a Database for Optimal Performance" />
</Exam>
<Exam ID="70-433" Desc="TS: Microsoft SQL Server 2008, Database Development">
<Obj ObjDesc="Implementing Tables and Views" />
<Obj ObjDesc="Implementing Programming Objects" />
<Obj ObjDesc="Working with Query Fundamentals" />
<Obj ObjDesc="Applying Additional Query Techniques" />
<Obj ObjDesc="Working with Additional SQL Server Components" />
<Obj ObjDesc="Working with XML Data" />
<Obj ObjDesc="Gathering Performance Information" />
</Exam>
</Exams>
‘
EXEC sp_xml_preparedocument @iHndl OUTPUT, @XmlDoc;
SELECT * FROM OPENXML (@iHndl, ‘/Exams/Exam/Obj’, 1) WITH (ExamID varchar(10) ‘../@ID’, ObjDesc varchar(100));
EXEC sp_xml_removedocument @iHndl;
What is the result?
A. The query returns output in XML format containing the exam ID and the objective description.
B. The query returns output in a tabular result set containing the exam ID and the objective description.
C. The query returns output in tabular format containing the exam ID, the exam description, and the objective description.
D. The query generates an error because the FROM clause is incorrect.
Correct Answer: B
Explanation/Reference:
The query returns output in a tabular result set containing the exam ID and the objective description. In this scenario, you use the OPENXML function. OPENXML is a rowset provider function that creates a relational view of data contained in an XML document. OPENXML can be used in place of a table or view in a SELECT statement. The OPENXML function accepts the following parameters:
idoc: Identifies an integer document handle returned using the sp_xml_preparedocument system stored procedure.
rowpattern: Specifies the name of the node from which processing should begin.
flag: Identifies the type of the mapping as either attribute-specific or element-specific. A flag value of 1 indicates that the mapping should be attribute-specific, and a value of 2 indicates that the mapping should be element-specific.
colpattern: Specifies an XPath pattern to identify the XML elements that should be mapped. This parameter is used with the WITH clause of the query.
In this scenario, you first call the sp_xml_preparedocument system stored procedure to return the document handle. Then, you include a query that contains the OPENXML function in the FROM clause. In this scenario, ‘/Exams/Exam/Obj ‘ specifies that processing should begin at the <Obj> node. Then, the WITH clause specifies the columns that should appear in the output. The columns will be output in the order they are specified. For each column in the WITH clause, you specify the column name and data type, and an optional XPath expression that specifies how mapping should occur. If you already have a table that has the desired columns, you can specify a table name instead of individually naming the columns. After the data has been extracted from the XML document, you call the sp_xml_removedocument stored procedure to remove the internal document handle and free resources. The Transact-SQL code in this scenario will return the following result set:
The option that states the query returns output in XML format is incorrect because the output is a tabular result set, not XML.
The option that states the query returns output in tabular format containing the exam ID, the exam description, and the objective descriptions is incorrect. This code only includes the value of the <Exam> element’s ID attribute and the value of the <Obj> elements ObjDesc attribute in the result set.
The option that states the query generates an error because the FROM clause is incorrect. The FROM clause in this code correctly specifies the use of OPENXML.
