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  )