Section 7.7. XQuery Support


7.7. XQuery Support

XQuery is a language for querying XML data. SQL Server 2005 supports a subset of XQuery for querying the xml data type. The implementation is aligned with the July 2004 draft of XQuery. For more information about using the XQuery language, see the World Wide Web Consortium (W3C) web site at http://www.w3.org/TR/2004/WD-xquery-20040723/ and Microsoft SQL Server 2005 Books Online.

7.7.1. xml Data Type Functions

The XQuery functions described in Table 7-6 can be used with XQuery against the xml data type. These functions are part of the http://www.w3.org/2004/07/xpath-functions namespace. The W3C specification uses a namespace prefix of fn: for these functions. However, use of fn: is not required in the SQL Server 2005 implementation.

Table 7-6. XQuery functions

Category

XQuery function

Description

Numeric

ceiling

Returns the smallest integer that is not smaller than the argument

 

floor

Returns the largest integer that is not larger than the argument

 

round

Returns the integer closest to the argument

String

concat

Returns a string concatenation of zero or more string arguments

 

contains

Returns an xs:boolean value indicating whether a string argument contains a string specified by a second argument

 

substring

Returns the specified part of a string argument

 

string-length

Returns the length of a string argument

Booleans

not

Returns a Boolean value for the logical NOT of a Boolean argument

Nodes

number

Returns the numeric value of a specified node

Context

last

Returns an unsigned integer indicating the number of items in the sequence currently being processed

 

position

Returns an unsigned integer indicating the number position of the context item in the sequence currently being processed

Sequences

empty

Returns a Boolean value indicating whether the specified sequence is an empty sequence

 

distinct-values

Returns a sequence containing the distinct values in a specified sequence

Aggregate

count

Returns the number of items in a specified sequence

 

avg

Returns the average of the values in a specified sequence

 

min

Returns the smallest value in a specified sequence

 

max

Returns the largest value in a specified sequence

 

sum

Returns the sum of the values in a specified sequence

Constructor

Constructor Functions

Creates an instance of any XSD built-in or user-defined atomic type

Data accessor

string

Returns the string representation of a node or atomic value argument

 

data

Returns the typed value of the specified node


The following query uses XQuery to retrieve the work center having the most labor hours for each product from the Instructions xml data type column in the Production.ProductModel table in AdventureWorks. Note that you must enter the emphasized line in the example on a single line.

     USE AdventureWorks     SELECT ProductModelID, Name,       Instructions.query('       declare namespace AWMI=         "http://schemas.microsoft.com/sqlserver/2004/07/           adventure-works/ProductModelManuInstructions";       for $Location in /AWMI:root/AWMI:Location       where $Location/@LaborHours = max(/AWMI:root/AWMI:Location/@LaborHours)       return <Location WC         LaborHrs="{ $Location/@LaborHours }" />') Result     FROM  Production.ProductModel     WHERE Instructions IS NOT NULL 

Partial results are shown in Figure 7-15.

Figure 7-15. Results for XQuery example


7.7.2. xml Data Type Operators

The XQuery operators described in Table 7-7 can be used in queries that run against xml data type instances.

Table 7-7. XQuery operators

Category

Operators

Numeric

+, -, *, div, mod

Value comparison

eq, ne, lt, gt, le, ge

General comparison

=, !=, <, >, <=, >=


7.7.3. Using XQuery Extension Functions to Bind Relational Data Inside XML Data

In addition to xml data type methods, SQL Server provides two XQuery extension functionssql:column( ) and sql:variable( )to bind relational data inside XML data. These functions bring in data from a non-xml data type column or from a T-SQL variable so that you can investigate or manipulate the relational data as you would an xml data type instance. Bound relational data is read-only.

The XQuery extension functions cannot be used to reference data in columns or variables of xml, CLR UDT, timestamp, text, ntext, sql_variant, or image data types.


7.7.3.1. sql:column( ) function

The sql:column( ) function exposes relational data from a non-xml data type column, letting you return relational data as part of an XML result set. The syntax is:

     sql:column(columnName  ) 

where:


columnName

The name of a column in the row being processed

The following example adds the ProductionModelID and Name columns and the SQL variable @laborHourTarget to the XML result containing the maximum labor hours for each product model from the Instructions xml data type column. Note that you must enter the emphasized line in the example on a single line.

     USE AdventureWorks     DECLARE @laborHourTarget int;     SET @laborHourTarget = 2.5;     SELECT Instructions.query('       declare namespace pmmi="http://schemas.microsoft.com/sqlserver/         2004/07/adventure-works/ProductModelManuInstructions";       <ProductModel         ProductModelID=   "{ sql:column("pm.ProductModelID") }"         Name=             "{ sql:column("pm.Name") }"         LaborHourTarget=  "{ sql:variable("@laborHourTarget") }" >         { attribute MaxLaborHours {max(/pmmi:root/pmmi:Location/@LaborHours)} }       </ProductModel>     ') AS Result     FROM Production.ProductModel pm     WHERE Instructions IS NOT NULL 

Partial results are shown in Figure 7-16.

Figure 7-16. Results for sql:column( ) function example


7.7.3.2. sql:variable( ) function

The sql:variable( ) function used in the preceding example exposes data in a non-xml data type T-SQL variable inside XML . The syntax is:

     sql:variable(variableName  ) 

where:


variableName

The name a T-SQL variable



Programming SQL Server 2005
Programming SQL Server 2005
ISBN: 0596004796
EAN: 2147483647
Year: 2007
Pages: 147
Authors: Bill Hamilton

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net