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.

by Klaus Aschenbrenner

Mar 23, 2004

Page 5 of 8

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

« Previous Page 1 2 3 456 7 8 Next Page »

0 Comments (click to add your comment)

Comment and Contribute