You are a database developer on an instance of SQL Server 2008. Your Prod database contains a POMaster table defined as follows:
The Details column contains the line items for each purchase order in the following XML format:
<POLines PONum="12" VendorName="VirtuArt Corporation">
<POLine>
<InvID>001</InvID>
<Quantity>25</Quantity>
<Cost>125.57</Cost>
<Taxable>1</Taxable>
</POLine>
<POLine>
<InvID>002</InvID>
<Quantity>12</Quantity>
<Cost>29.95</Cost>
<Taxable>0</Taxable>
</POLine>
<POLine>
<InvID>003</InvID>
<Quantity>100</Quantity>
<Cost>2.25</Cost>
<Taxable>1</Taxable>
</POLine>
</POLines>
You want to query the POMaster table and return the PurchaseOrderID and OrderDate. You also want the query to return the PONum and VendorName attributes from the XML stored in the Details column for the purchase order.
Which query should you execute?
A. SELECT PurchaseOrderID, OrderDate,
Details.value(‘(@VendorName) [1]’, ‘varchar(30)’) AS Vendor,
Details.value(‘(@PONum)[1]’, ‘int’) AS PONumber
FROM POMaster;
B. SELECT PurchaseOrderID, OrderDate,
Details.value(‘(/POLines/@VendorName) [1]’, ‘varchar(30)’) AS Vendor,
Details.value(‘(/POLines/@PONum)[1]’, ‘int’) AS PONumber
FROM POMaster;
C. SELECT PurchaseOrderID, OrderDate,
Details.value(‘(/POLines/VendorName) [1]’, ‘varchar(30)’) AS Vendor,
Details.value(‘(/POLines/PONum)[1]’, ‘int’) AS PONumber
FROM POMaster;
D. SELECT PurchaseOrderID, OrderDate,
Details.query(‘(/POLines/@VendorName) [1]’, ‘varchar(30)’) AS Vendor,
Details.query(‘(/POLines/@PONum)[1]’, ‘int’) AS PONumber
FROM POMaster;
Correct Answer: B
Explanation/Reference:
This query uses the XML value() method to return specific attribute values from the XML stored in the Details column. The value() method accepts two arguments. The first argument is a string XQuery expression, and the second argument is a string containing a SQL Server data type. The value() method extracts a value using the XPath expression and returns it as the specified type. In this scenario, you wanted to return the PurchaseOrderID and OrderDate columns from the table and two additional attributes from the XML stored in the Details column. The given SELECT statement uses the value() method to return a Vendor column containing a varchar(30) value extracted from the VendorName attribute in the <POLines> element of the XML. The statement also uses the value() method to return a PONumber column containing an int value extracted from the PONum attribute in the <POLines> element of the XML. The [1] at the end of each string expression indicates that the information is extracted from the first <POLines> element. In this scenario, only one <POLines> element exists.
You should note that the expression string you specify must return a single value that can be successfully converted to the specified data type. If you specify an expression that returns multiple values, or an expression that cannot be successfully converted to the specified SQL Server data type, an error will occur.
You should not execute the following query:
SELECT PurchaseOrderID, OrderDate, Details.value(‘(@VendorName) [1]’, ‘varchar(30)’) AS Vendor,
Details.value(‘(@VendorName) [1]’, ‘varchar(30)’) AS Vendor, Details.value(‘(@PONum)[1]’, ‘int’) AS PONumber
FROM POMaster;
This query will return an error because the expression in the first argument begins with an attribute instead of an element. This is not allowed because attributes can only be defined within elements. This statement generates the following error:
Msg 2390, Level 16, State 1, Line 5
XQuery [POMaster.Details.value()]: Top-level attribute nodes are not supported.
You should not execute the following query:
SELECT PurchaseOrderID, OrderDate, Details.value(‘(/POLines/VendorName) [1]’, ‘varchar(30)’) AS Vendor,
Details.value(‘(/POLines/VendorName) [1]’, ‘varchar(30)’) AS Vendor, Details.value(‘(/POLines/PONum)[1]’, ‘int’) AS PONumber
FROM POMaster;
This query attempts to return the value of elements named VendorName and PONum. In this scenario, the XML does not contain elements with these names. Instead, these are attributes, which should be prefixed with an ampersand (@) in the expressions. This query will execute, but will return a NULL value for both Vendor and PONumber in the result
You should not execute the following query:
SELECT PurchaseOrderID, OrderDate, Details.query(‘(/POLines/@VendorName) [1]’, ‘varchar(30)’) AS Vendor,
Details.query(‘(/POLines/@VendorName) [1]’, ‘varchar(30)’) AS Vendor, Details.query(‘(/POLines/@PONum)[1]’, ‘int’) AS PONumber
FROM POMaster;
This query returns the following error because the query() method only accepts a single argument:
Msg 174, Level 15, State 1, Line 4
The query function requires 1 argument(s).
Even if the syntax were corrected, using the query() method would not be appropriate in this scenario. The query() method is used to query and retrieve XML elements and attributes from an XML instance. The method accepts a string XQuery expression that determines which elements and attributes are extracted, and returns untyped XML, not scalar values. For example, suppose you used the following query:
SELECT PurchaseOrderID, OrderDate, Details.query(‘/POLines[@VendorName]’) AS Vendor,
Details.query(‘/POLines[@VendorName]’) AS Vendor, Details.query(‘/POLines[@PONum]’) AS PONumber
FROM POMaster;
The query would successfully execute, but would contain untyped XML in the Vendor and PONumber columns of the result.