Browse DevX
Sign up for e-mail newsletters from DevX


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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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" />

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date