Saturday, September 16, 2006 11:34 AM
xmldeveloper
70-442 Case Study
Let's take a look at the following example from Books Online:
DECLARE @price money
SET @price=2500.00
SELECT CatalogDescription.query('
declare namespace pd="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
<Product
ProductID="{ sql:column("Production.Product.ProductID") }"
ProductModelID= "{ sql:column("Production.Product.ProductModelID") }"
ProductModelName="{/pd:ProductDescription[1]/@ProductModelName }"
ListPrice="{ sql:column("Production.Product.ListPrice") }"
DiscountPrice="{ sql:variable("@price") }"
/>')
FROM Production.Product
JOIN Production.ProductModel
ON Production.Product.ProductModelID = Production.ProductModel.ProductModelID
WHERE ProductID=771
-- Output
<Product ProductID="771" ProductModelID="19" ProductModelName="Mountain 100" ListPrice="3399.99" DiscountPrice="2500" />
What do we have here:
- The xml result is constructed using the query() method and XQuery language.
- The value for ProductID is obtained from a non-XML column using the sql:column() function to bind this value in the XML.
- The same method is used for the value of ListPrice from a non-XML column of another table.
- The value of DiscountPrice is taken from a Transact-SQL variable using the sql:variable() function.
- Last the value of ProductModelName is taken from an xml type column.
If you intent to take the 70-442 you should be familiar with all the above methods and functions: query(), sql:column(), sql:variable().