You are a database developer on an instance of SQL Server 2008. In your Prod database, you have purchasing-related tables that contain product, vendor, and purchasing data. You want extract data from several of these tables and display it in XML format. You want to accomplish this with the minimum development effort. Which action should you take?
A. Create a stored procedure that queries the tables and returns an xml data type.
B. Use the FOR XML clause in a query that joins the required tables.
C. Create a Transact-SQL script that uses the sp_xml_preparedocument system stored procedure and then inserts the XML into an xml data type column.
D. Implement a query that uses the OPENXML function in the FROM clause
Correct Answer: B
Explanation/Reference:
You should use the FOR XML clause in a query that joins the required tables. 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 will 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 in which the columns are specified in the SELECT list.
EXPLICIT: Each row in the rowset can be defined in detail, including 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.
You can also include the ELEMENTS option with the FOR XML clause. This will return columns in the SELECT list as subelements, rather than as attributes. Each table specified in the FROM clause will be represented by a separate element, and each column from that table will appear as a subelement of that element. Tables specified first will constitute higher-level elements of the hierarchy, and if specified, column aliases will be used as element names. For example, in this scenario, you might use the following query to extract data from the Product, ProductVendor, and Vendor tables in XML format:
SELECT p.Name As Product, v.Name AS Vendor, p.ProductID
FROM Production.Product p
INNER JOIN Purchasing.ProductVendor pv
ON p.ProductID = pv.ProductID
INNER JOIN Purchasing.Vendor v
ON pv.VendorID = v.VendorID
FOR XML RAW, ELEMENTS;
The query would display the XML in the following format:
<row>
<Product>Product A</Product>
<Vendor>VirtuArt, Inc.</Vendor>
<ProductID>1</ProductID>
</row>
<row>
<Product>Product B</Product>
<Vendor>NuTex Corporation</Vendor>
<ProductID>879</ProductID>
</row>
<row>
<Product>Product C</Product>
<Vendor>VirtuArt, Inc.</Vendor>
<ProductID>712</ProductID>
</row>
<row>
<Product>Product D</Product>
<Vendor>InterConn</Vendor>
<ProductID>2</ProductID>
</row>
You should not implement a query that uses the OPENXML function in the FROM clause, or create a Transact-SQL script that uses the sp_xml_preparedocument system stored procedure and then inserts the XML into an xml data type column. OPENXML is a rowset provider function that creates a relational view of data contained in an XML document. This function can be used in SELECT statements where a table or view would be specified to extract data from an XML document.
One of the function’s required parameters is an integer used as a handle to the internal representation of the XML document. Therefore, before you can extract data from an XML document, you must call the sp_xml_preparedocument stored procedure to create and return the document handle. After the data has been extracted from the XML document by a SELECT query with the OPENXML function, you would call the sp_xml_removedocument stored procedure to remove the internal representation of the document and free resources.
You should not create a stored procedure that queries the tables and returns an xml data type. In this scenario, you wanted to display the data in XML format.
Therefore, you would still have to display the result of the returned xml data type.
