Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

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

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.


advertisement
XML Datatype Methods
With the help of these four functions, you can now embed XQuery statements directly into T-SQL code:
  • modify(): This method updates XML data. Since these updates also use the XML Data Modification Language (XML DML), I'll disuss this method later in more detail.
  • query(): This method executes a XQuery statement on an XML datatype. The result is an untyped XML fragment:

    DECLARE @myDoc xml SET @myDoc = '<Root> <ProductDescription ProductID="1" ProductName="Road Bike"> <Features> <Warranty>1 year</Warranty> <Maintenance>3 years</Maintenance> </Features> </ProductionDescription> </Root>' SELECT @myDoc::query('/Root/ProductDescription/Features') -- Result <Features> <Warranty>1 year</Warranty> <Maintenance>3 years/Maintenance> </Features>

    These functions are not limited to use on XML variables. The following code shows how to use query() on a XML column:


    USE AdventureWorks SELECT CatalogDescription::query(' namespace PD="http://www.adventure-works.com/ schemas/products/description" <Product ProductModelID="{ /PD:ProductDescription[1]/ @ProductModelID }" />)' AS Result FROM ProductModel WHERE CatalogDescription::exist('namespace PD=" http://www.adventure-works.com/schemas/product/ description" namespace wm="http://www.adventure-works. com/schemas/products/WarrAndMain" /PD:ProductDescription/PD:Features/wm:Warranty') = 1 -- Result <Product ProductModelID="19" /> <Product ProductModelID="23" /> <Product ProductModelID="25" /> <Product ProductModelID="28" /> <Product ProductModelID="34" /> <Product ProductModelID="35" />

    In the previous code, the column CatalogDescription is a typed XML column. Because it is, you need to provide the XML namespace names in the XQuery statements. This query constructs the XML element <Product>, which consists of the attribute ProductIDS. From the column CatalogDescription, you only return the elements that contain the <Warranty> element. This example shows very nicely how T-SQL code can be combined with XQuery statements.
  • value(): This method returns a native SQL Server type like datetime, varchar, or int. Here's what this looks like:

    USE AdventureWorks SELECT CatalogDescription::value('namespace PD="http://www.adventure-works.com/schemas/products/ deescription" /PD:ProductDescription/@ProductModelID', 'int') AS Result FROM ProductModel ORDER BY Result DESC

    value() expects a XQuery statement in the first parameter and the native SQL Server datatype to which the result should be converted in the second parameter. The following table shows how the most important XSD datatypes are mapped to the SQL Server datatypes:

    XSD datatype

    SQL Server datatype

    string

    nvarchar(4000)

    boolean

    bit

    decimal

    numeric

    float

    real

    double

    float

    duration

    varbinary

    datetime

    varbinary

    time

    varbinary

    date

    varbinary

  • exist(): This method determines if a specified node or value exists in the XML data. exist() returns 1 (true) if the node/value exists and returns 0 (false) if it doesn't. The following code uses exist() to determine whether a <WorkCenter> element with the ID 50 exists in the XML data:

    DECLARE @x xml ('http://schemas.adventure- works.com/ManufInstructions/ByProdModel') SELECT @x = Instructions FROM ProductPlan WHERE ProductModelID = 67 DECLARE @f int SET @f = @x::exist('namespace AWMI=" http://schemas.adventure-works.com/ ManufInstructions/ByProdModel" /AWMI:root/AWM:WorkCenter[@WorkCenterID=50]') SELECT @f



Comment and Contribute

 

 

 

 

 


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

 

 

Sitemap