You are a database developer on an instance of SQL Server 2008. Your Sales database contains sales and marketing information for your company. You receive an XML file in the following format that contains leads from sales representatives in the field:
<LeadData>
<Salesperson ID="45">
<DailyLeads>
<Leads ID="22" InitDate="2009-01-07" >
<Company Name="NuTex Corporation" EstRev="40000" />
<Company Name="Verigon Inc." EstRev="125000" />
</Leads>
</DailyLeads>
</Salesperson>
<Salesperson ID="82">
<DailyLeads>
<Leads ID="29" InitDate="2009-02-12" >
<Company Name="VisionWorx" EstRev="500000" />
<Company Name="InterConn" EstRev="187000" />
</Leads>
</DailyLeads>
</Salesperson>
</LeadData>
You load the XML into an xml variable named @x and execute the following Transact-SQL:
EXEC sp_xml_preparedocument @idoc OUTPUT, @x
SELECT * INTO dbo.SalesLeads
FROM OPENXML(@idoc, ‘LeadData/Salesperson’, 1)
WITH (ID int, Leads xml ‘DailyLeads’);
EXEC sp_xml_removedocument @idoc;
What will be the result?
A. An XML document is created containing each salesperson’s leads.
B. A single row is inserted into the SalesLeads table.
C. Multiple rows are inserted into the SalesLeads table.
D. The statement fails because the OPENXML function is incorrectly specified.
Correct Answer: C
Explanation/Reference:
In this scenario, you first call the sp_xml_preparedocument stored procedure to create an internal handle to the XML document. Then, you include a query that contains the OPENXML function in the FROM clause. The OPENXML function is used to extract data from an XML document and present it as a relational rowset.
This function can be used in SELECT statements where a table or view would be specified to extract data from an XML document into relational format. The OPENXML function accepts the following parameters:
idoc: Identifies the 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 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, the row pattern ‘LeadData/Salesperson’ specifies that processing should begin at the <Salesperson> node. The flagvalue of 1 indicates that mapping is attribute-specific. 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, you should specify the column name and data type, and an optional XPath expression. If you already have a table that has the desired columns, you can specify a table name instead of individually naming the columns. In this scenario, you would specify the ID and Leads columns in the WITH clause. The ID column will contain the ID attribute value for each salesperson as an int data type. The Leads column is an xml data type. This will generate a rowset that contains one row per salesperson. The Leads column of each row will contain the XML for each <Leads> element for the salesperson. You also include an INTO clause in the query to insert the result set into a new SalesLeads table. In this scenario, the Leads column for the salesperson with an ID value of 82 would contain the following XML:
<DailyLeads>
<Leads ID="29" InitDate="2009-02-12">
<Company Name="VisionWorx" EstRev="500000" />
<Company Name="InterConn" EstRev="187000" />
</Leads>
</DailyLeads>
After the data has been extracted from the XML document, you should call the sp_xml_removedocument stored procedure to remove the internal handle and free resources.
The option that states an XML document is created containing each salesperson’s leads is incorrect because this query returns data in relational format, rather than XML.
The option that states a single row is inserted into the SalesLeads table is incorrect. Because you specified that the extraction should begin with the <Salesperson> node, a row will be inserted for each salesperson.
The option that states the statement fails because the OPENXML function is incorrectly specified is incorrect. The statement is syntactically correct and will not generate an error.
