Kiss the Middle-tier Goodbye with SQL Server Yukon : Page 6

Yukon's new XML support features cut out the middleman and allow you to deal with your data directly on the database tier. Learn how to use these features to improve your database app's performance and design.

by Klaus Aschenbrenner

Mar 23, 2004

Page 6 of 8

XQuery Extended
You can see that Yukon uses XQuery statements to produce some very powerful T-SQL code. But what if you want to reference another column or variable within your XQuery statement? The XQuery specification doesn't provide any support for this. For that reason, Microsoft has extended XQuery with two important functions:

sql:column(): This function accesses columns within your XQuery statement. To do this, your column name must be fully qualified with the table name, for instance Customer.CustomerID. Take a look:


USE AdventureWorks

SELECT P.ProductID, CatalogDescription::query('
   namespace pd="http://www.adventure-works.com/
   schemas/products/description"
   <Product
      ProductID="{ sql:column("P.ProductID") }"
      ProductName="{ sql:column("P.Name") }"
      ProductPrice="{ sql:column("P.ListPrice") }"
      ProductModelID="{ sql:column("PM.ProductModelID") }
      ">
      {
         if (not(empty(/pd:ProductDescription)))
         then
            attribute ProductModelName
            {
               /pd:ProductDescription[1]/
               @ProductModelName
            }
         else ()
      }
   </Product>' AS Result
FROM ProductModel PM, Product P
WHERE PM.ProductModelID = P.ProductModelID
ORDER BY PM.ProductModelID

-- Result
<Product ProductID="1225" ProductName="Classic 
   Vest, S" ProductPrice="63.5" ProductModelID = "1" />
...

Notice that the values of the attributes ProductID, ProductName, ProductPrice, and ProductModelID are taken from the corresponding columns in the tables Product and ProductModel. In contrast to this, the attribute ProductModelName is taken from the XML column CatalogDescription.

sql:variable(): This function references a variable (like in a stored procedure) within the XQuery statement. Here's how to use this function:






USE AdventureWorks

DECLARE @price money
SET @price = 2500.00
DECLARE @x xml

SELECT @x = CatalogDescription::query('
   namespace pd="http://www.adventure-works.com/schemas/
   products/description"
   <Product
      ProductID="{ sql:column(Product.ProductID") }"
      ProductModelID="{ 
         sql:column(Product.ProductModelID") }"
      ProductModelName="{ /pd:ProductDescription[1]
         /@ProductModelName }"
      ListPrice="{ sql:column("Product.ListPrice") }"
      DiscountPrice="{ sql:variable("@price") }"
   />'
FROM Product, ProductModel
WHERE Product.ProductModelID = 
   ProductModel.ProductModelID AND ProductID = 168

SELECT @x

-- Result
<Product ProductID="168" 
   ProductModelID="19" 
   ProductModelName="Mountain 100" 
   ListPrice="3374.99" 
   DiscountPrice="2500.00" />

« Previous Page 1 2 3 4 567 8 Next Page »

0 Comments (click to add your comment)

Comment and Contribute