A Simple Query

To compare SQL and XQuery queries, we will first start with our simple product catalog document. A basic SQL query might select all the values from the table that meet some specific criteria, for example, those in the ACC department. The SQL statement that accomplishes this is:

select * from catalog
where dept='ACC'

In XQuery, we can use a straight path expression for such a simple query, as in:

doc("catalog2.xml")//product[@dept='ACC']

If you don’t want to sort your results or construct new elements, it is often simpler (and possibly faster) to just use a path expression. However, we could also use a full FLWOR expression that uses a where clause similar to SQL, as in:

for $prod in doc("catalog2.xml")//product
where $prod/@dept='ACC'
return $prod

In the where clause, we need to start the reference to the dept attribute with $prod/ in order to give it some context. This is different from SQL, where if there is only one dept column in the table(s) in the query, it is assumed to be that one column. In XQuery, you must be explicit about where the dept attribute appears, because it could appear on any level of the document.

Now, suppose we want to sort the values by the product number. In SQL, we would simply add an order by clause, as in:

select * from catalog
where dept='ACC'
order by number

We would also add an order by clause to the FLWOR, again giving it the context, as in:

for $prod in doc("catalog2.xml")//product
where $prod/@dept='ACC'
order by $prod/number
return $prod

Conditions and Operators

Conditions and operators are used to filter query results. Many of the conditions and operators available in SQL can also be used in XQuery, although sometimes with a slightly modified syntax.

for $prod in doc("catalog2.xml")//product
where $prod/number > 500 and $prod/number < 700
return $prod

select * from catalog
where name LIKE 'F%'

for $prod in doc("catalog2.xml")//product
where starts-with($prod/name, 'F')
return $prod

for $prod in doc("catalog2.xml")//product
where matches($prod/name, '^F.*')
return $prod

select * from catalog
where dept in ('ACC', 'WMN')

for $prod in doc("catalog2.xml")//product
where $prod/@dept = ('ACC', 'WMN')
return $prod

select * from catalog
where dept='ACC' and
      (name like 'F%' or name like 'G%')

for $prod in doc("catalog2.xml")//product
where $prod/@dept='ACC' and 
      (matches($prod/name, '^F') or matches($prod/name, '^G'))
return $prod

select * from catalog
where not(dept='ACC') and
      (name not like 'F%')

for $prod in doc("catalog2.xml")//product
where not($prod/@dept='ACC') and
      not(matches($prod/name, '^F'))
return $prod

Functions

SQL has a number of built-in functions, many of which have equivalent XQuery functions, often with the same name. Some of the commonly used SQL functions and their XQuery equivalents are listed in Table 26-4. The syntax to call functions is the same in both languages: the arguments are enclosed in parentheses and separated by commas.

Selecting Distinct Values

SQL has a DISTINCT keyword that allows only distinct values to be selected. For example, to get a list of the unique departments in the catalog, you would use the query:

select distinct dept from catalog

In XQuery, you would make a call to the distinct-values function, as in:

distinct-values(doc("catalog2.xml")//product/@dept)

or, if you prefer a FLWOR:

for $value in distinct-values(doc("catalog2.xml")//product/@dept)
return $value

Often you are interested in a combination of distinct values. In SQL, this is quite straightforward; you simply add more columns to the query. To get the distinct combinations of department and product number, you could use:

select distinct dept, number from catalog

However, the XQuery distinct-values function only accepts one set of values. This means that you must use a FLWOR expression with multiple for clauses to achieve the same result. This is shown in Example 26-2 and described further in “Selecting Distinct Values”.

for $d in distinct-values(doc("catalog2.xml")//product/@dept),
    $n in distinct-values(doc("catalog2.xml")//product[@dept = $d]/number)
return <result dept="{$d}" number="{$n}"/>

<result dept="WMN" number="557"/>
<result dept="ACC" number="563"/>
<result dept="ACC" number="443"/>
<result dept="MEN" number="784"/>

Working with Multiple Tables and Subqueries

Many SQL queries join multiple tables together. For example, suppose you want to join the order and product tables to retrieve results that contain values from both tables. In SQL, you might write a query such as:

select order_item.num, catalog.name, order_item.quantity
from order_item, catalog
where order_item.num = catalog.number

In XQuery, joins are not needed so frequently because the data will often be stored in a single hierarchy rather than being split across multiple tables. However, joins still arise and the mechanism is similar. In XQuery, the join in the where clause might look like Example 26-3.

for $item in doc("order.xml")//item,
    $prod in doc("catalog2.xml")//product
where $item/@num = $prod/number
return <item num="{$item/@num}"
             name="{$prod/name}"
             quan="{$item/@quantity}"/>

<item num="557" name="Fleece Pullover" quan="1"/>
<item num="563" name="Floppy Sun Hat" quan="1"/>
<item num="443" name="Deluxe Travel Bag" quan="2"/>
<item num="784" name="Cotton Dress Shirt" quan="1"/>
<item num="784" name="Cotton Dress Shirt" quan="1"/>
<item num="557" name="Fleece Pullover" quan="1"/>

The XQuery example constructs an item element to hold each set of three values. This is because XQuery by default does not return the result of each evaluation of the return clause in a “row” or any other container. If you simply returned the three values for each product, as in:

return ($item/@num, $prod/name, $item/@quantity)

the result would be 18 sequential values (557, Fleece Pullover, 1, 563, Floppy Sun Hat, etc.), with no relationship among them. The item element serves as a container to group the related three values together—the same purpose a row would serve in an SQL result.

As an alternative to a where clause, you can use one or more predicates in your for clause, as in:

for $item in doc("order.xml")//item,
    $prod in doc("catalog2.xml")//product[number = $item/@num]
return <item num="{$item/@num}"
             name="{$prod/name}"
             quan="{$item/@quantity}"/>

More information on joins in XQuery can be found in “Joins”, including examples of three-way joins and outer joins.

select *
from catalog
where number in (select num from order_item
                 where ordnum = '00299432')

for $prod in doc("catalog2.xml")//product
where $prod/number =
    (for $item in doc("order.xml")/order[@num='00299432']/item
     return $item/@num)
return $prod

Grouping

SQL allows you to group data by certain values. For example, if you want to count the number of products in each department, you can use the query:

select dept, count(*)
from catalog
group by dept

XQuery has a similar group by clause, as shown in Example 26-4.

xquery version "3.0";
for $prod in doc("catalog2.xml")//product
group by $d := $prod/@dept
return <result dept="{$d}" count="{count($prod)}"/>

<result code="ACC" count="2"/>
<result code="MEN" count="1"/>
<result code="WMN" count="1"/>

As you can see, in order to construct a “table” of return values, we construct result elements with two attributes representing the “columns.” Grouping is covered in more detail in “Grouping”.

Combining Structured and Semi-Structured Data

One use case is to combine narrative text with more highly structured data. An example is when each of the products has a textual description that can span multiple paragraphs and can be marked up with HTML-like tags to indicate sections of text that need to be in bold or italic. This is shown in Table 26-6.

When you create the table, you declare the desc column to be of type XML, as in:

create table prod_desc (
  number integer not null,
  desc xml
  );

If desired, you can specify the location of a schema for the desc column, which will ensure that any values inserted into desc conform to that schema. It will also provide all the other benefits of using a schema, such as query debugging.

Flexible Data Structures

Another use case for storing XML in a relational table is to take advantage of the flexibility of XML. Suppose each product has a set of custom properties that needs to change flexibly over time. It is possible to create columns on the catalog table for each property, but that is inflexible because a new column needs to be added when a new property is added, also necessitating changes in the application that reads and writes to the table.

Another approach might be to create generic columns named property1, property2, etc., but this is problematic because you don’t know how many columns to create, and you need some sort of mapping scheme to figure out what each column means for each product. A more flexible approach might be to store the properties as XML, as shown in Table 26-7. This allows them to be queried and even indexed, but does not force a rigid database structure.

<properties>
  <sleeveLength>24</sleeveLength>
</properties>

<properties/>

<properties>
  <capacity>80</capacity>
</properties>

<properties>
  <sleeveLength>25</sleeveLength>
  <extraButtons>2</extraButtons>
</properties>

To constrain the query based on the contents of the desc column, you can use an XQuery expression embedded in your SQL statement. In the case of SQL Server, one way to do this is through the use of an exist function. For example:

select number, properties
from prod_properties
where properties.exist('/properties/sleeveLength[. > 20]') = 1

The SQL Server exist function returns true (which equals 1) if there are any nodes that match the criteria. The expression in parentheses passed to the exist function is in XQuery syntax, and it is evaluated relative to the root of the document in the properties column, for each row. The results are shown in Table 26-8.

<properties>
  <sleeveLength>24</sleeveLength>
</properties>

<properties>
  <sleeveLength>25</sleeveLength>
  <extraButtons>2</extraButtons>
</properties>

To return only the sleeveLength element, you could use another SQL Server function called query in the select clause, as in:

select number,
       properties.query('//sleeveLength') slength
from prod_properties
where properties.exist('/properties/sleeveLength[. > 20]') = 1

Like the exist function, the SQL Server query function accepts an XQuery query as a parameter. The string slength is used to provide a name for the column in the results. This will return a sleeveLength element for each row, as shown in Table 26-9.

If you want to further reduce the result set to the contents of the sleeveLength element, you can call the XQuery data function in the query, as in:

select number,
       properties.query('data(//sleeveLength)') slength
from prod_properties
where properties.exist('/properties/sleeveLength[. > 20]') = 1

The results are shown in Table 26-10.

Alternatively, you can use the SQL Server value function, which requires you specify a type for the value, as in:

select properties.value('(/properties/sleeveLength)[1]', 'integer') slength
from prod_properties
where properties.exist('/properties/sleeveLength[. > 20]') = 1

The SQL Server value function forces the use of the [1] predicate to ensure that only a single node is returned. If a schema were in use, and it specified that there could only ever be one sleeveLength child of properties, this would not be necessary.