XQuery is, like XML, case sensitive. Therefore, if you want to check the examples manually, you have to write the queries exactly as they are written in this chapter. For example, if you write Data() instead of data(), you will get an error stating that there is no Data() function.

XQuery returns sequences. Sequences can include atomic values or complex values (XML nodes). Any node, such as an element, attribute, text, processing instruction, comment, or document, can be included in the sequence. Of course, you can format the sequences to get well-formed XML. The following code shows different sequences returned from a simple XML instance by three XML queries.

DECLARE @x AS XML;
SET @x=N'
<root>
<a>1<c>3</c><d>4</d></a>
<b>2</b>
</root>';
SELECT
@x.query('*') AS Complete_Sequence,
@x.query('data(*)') AS Complete_Data,
@x.query('data(root/a/c)') AS Element_c_Data;
Here are the sequences returned.
Complete_Sequence Complete_Data Element_c_Data

<root><a>1<c>3</c><d>4</d></a><b>2</b></root> 1342 3

The first XQuery expression uses the simplest possible path expression, which selects everything from the XML instance; the second uses the data() function to extract all atomic data values from the complete document; the third uses the data() function to extract atomic data from the element c only.

Every identifier in XQuery is a qualified name, or a QName. A QName consists of a local name and, optionally, a namespace prefix. In the preceding example, root, a, b, c, and d are QNames; however, they are without namespace prefixes. The following standard namespaces are predefined in SQL Server:

  • xs The namespace for an XML schema (the uniform resource identifier, or URI, is http://www.w3.org/2001/XMLSchema)
  • xsi The XML schema instance namespace, used to associate XML schemas with instance documents (http://www.w3.org/2001/XMLSchema-instance)
  • xdt The namespace for XPath and XQuery data types (http://www.w3.org/2004/07/xpath-datatypes)
  • fn The functions namespace (http://www.w3.org/2004/07/xpath-functions)
  • sqltypes The namespace that provides mapping for SQL Server data types(http://schemas.microsoft.com/sqlserver/2004/sqltypes)
  • xml The default XML namespace http://www.w3.org/XML/1998/namespace)

You can use these namespaces in your queries without defining them again. You define your own data types in the prolog, which belongs at the beginning of your XQuery. You separate the prolog from the query body with a semicolon. In addition, in T-SQL, you can declare namespaces used in XQuery expressions in advance in the WITH clause of the T-SQL SELECT command. If your XML uses a single namespace, you can also declare it as the default namespace for all elements in the XQuery prolog.
You can also include comments in your XQuery expressions. The syntax for a comment is text between parentheses and colons: (: this is a comment :). Do not mix this with comment nodes in your XML document; this is the comment of your XQuery and has no influence on the XML returned. The following code shows all three methods of namespace declaration and uses XQuery comments. It extracts orders for the first customer from an XML instance.

DECLARE @x AS XML;
SET @x='
<CustomersOrders xmlns:co="TK461-CustomersOrders">
<co:Customer co:custid="1" co:companyname="Customer NRZBB">
<co:Order co:orderid="10692" co:orderdate="2007-10-03T00:00:00" />
<co:Order co:orderid="10702" co:orderdate="2007-10-13T00:00:00" />
<co:Order co:orderid="10952" co:orderdate="2008-03-16T00:00:00" />
</co:Customer>
<co:Customer co:custid="2" co:companyname="Customer MLTDN">
<co:Order co:orderid="10308" co:orderdate="2006-09-18T00:00:00" />
<co:Order co:orderid="10926" co:orderdate="2008-03-04T00:00:00" />
</co:Customer>
</CustomersOrders>';
-- Namespace in prolog of XQuery
SELECT @x.query('
(: explicit namespace :)
declare namespace co="TK461-CustomersOrders";
//co:Customer[1]/*') AS [Explicit namespace];
-- Default namespace for all elements in prolog of XQuery
SELECT @x.query('
(: default namespace :)
declare default element namespace "TK461-CustomersOrders";
//Customer[1]/*') AS [Default element namespace];
-- Namespace defined in WITH clause of T-SQL SELECT
WITH XMLNAMESPACES('TK461-CustomersOrders' AS co)
SELECT @x.query('
(: namespace declared in T-SQL :)
//co:Customer[1]/*') AS [Namespace in WITH clause];
Here is the abbreviated output.
Explicit namespace
--------------------------------------------------------------------------------
<co:Order xmlns:co="TK461-CustomersOrders" co:orderid="10692" co:orderd
Default element namespace
--------------------------------------------------------------------------------
<Order xmlns="TK461-CustomersOrders" xmlns:p1="TK461-Customers
Namespace in WITH clause
--------------------------------------------------------------------------------
<co:Order xmlns:co="TK461-CustomersOrders" co:orderid="10692" co:orderd

—————————————

Note: The Default Namespace
If you use a default element namespace, the namespace is not included for the elements in the resulting XML; it is included for the attributes. Therefore, only the first and third queries are completely equivalent. In addition, when you use the default element namespace, you can’t define your own namespace abbreviation. You should prefer an explicit namespace definition to using the default element namespace.

The queries used a relative path to find the Customer element. Before looking at all the  different ways of navigation in XQuery, you should first read through the most important XQuery data types and functions, described in the following two sections.

XQuery Data Types

XQuery uses about 50 predefined data types. Additionally, in the SQL Server implementation you also have the sqltypes namespace, which defines SQL Server types. You already know about SQL Server types. Do not worry too much about XQuery types; you’ll never use most of them. This section lists only the most important ones, without going into details about them.
XQuery data types are divided into node types and atomic types. The node types include attribute, comment, element, namespace, text, processing-instruction, and documentnode.
The most important atomic types you might use in queries are xs:boolean, xs:string, xs:QName, xs:date, xs:time, xs:datetime, xs:float, xs:double, xs:decimal, and xs:integer.
You should just do a quick review of this much-shortened list. The important thing to understand is that XQuery has its own type system, that it has all of the commonly used types you would expect, and that you can use specific functions on specific types only. Therefore, it is time to introduce a couple of important XQuery functions.

XQuery Functions

Just as there are many data types, there are dozens of functions in XQuery as well. They are  organized into multiple categories. The data() function, used earlier in the chapter, is a data accessor function. Some of the most useful XQuery functions supported by SQL Server are:

  • Numeric functions ceiling(), floor(), and round()
  • String functions concat(), contains(), substring(), string-length(), lower-case(), and upper-case()
  • Boolean and Boolean constructor functions not(), true(), and false()
  • Nodes functions local-name() and namespace-uri()
  • Aggregate functions count(), min(), max(), avg(), and sum()
  • Data accessor functions data() and string()
  • SQL Server extension functions sql:column() and sql:variable()

You can easily conclude what a function does and what data types it supports from the function and category names. For a complete list of functions with detailed descriptions, see the Books Online for SQL Server 2012 article “XQuery Functions against the xml Data Type” at http://msdn.microsoft.com/en-us/library/ms189254.aspx.

The following query uses the aggregate functions count() and max() to retrieve information about orders for each customer in an XML document.

DECLARE @x AS XML;
SET @x='
<CustomersOrders>
<Customer custid="1" companyname="Customer NRZBB">
<Order orderid="10692" orderdate="2007-10-03T00:00:00" />
<Order orderid="10702" orderdate="2007-10-13T00:00:00" />
<Order orderid="10952" orderdate="2008-03-16T00:00:00" />
</Customer>
<Customer custid="2" companyname="Customer MLTDN">
<Order orderid="10308" orderdate="2006-09-18T00:00:00" />
<Order orderid="10926" orderdate="2008-03-04T00:00:00" />
</Customer>
</CustomersOrders>';
SELECT @x.query('
for $i in //Customer
return
<OrdersInfo>
{ $i/@companyname }
<NumberOfOrders>
{ count($i/Order) }
</NumberOfOrders>
<LastOrder>
{ max($i/Order/@orderid) }
</LastOrder>
</OrdersInfo>
');

As you can see, this XQuery is more complicated than previous examples. The query uses iterations, known as XQuery FLWOR expressions, and formats the XML returned in the return part of the query. The FLWOR expressions are discussed later in this lesson. For now, treat this query as an example of how you can use aggregate functions in XQuery. The result of this query is as follows.

<OrdersInfo companyname="Customer NRZBB">
<NumberOfOrders>3</NumberOfOrders>
<LastOrder>10952</LastOrder>
</OrdersInfo>
<OrdersInfo companyname="Customer MLTDN">
<NumberOfOrders>2</NumberOfOrders>
<LastOrder>10926</LastOrder>
</OrdersInfo>