Section 7.7.  XQuery Support

JavaScript Editor Ajax toolkit     Ajax tutorials 



Main Page

Previous Page
Next Page

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 WCID="{ $Location/@LocationID }"
        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


Previous Page
Next Page


JavaScript Editor Ajax toolkit     Ajax tutorials