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