To compare SQL and XQuery queries, will we 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 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

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 24-5. The syntax to call functions is the same in both languages: the arguments are enclosed in parentheses and separated by commas.

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 24-2 and described further in "Selecting Distinct Values" in Chapter 6.

Query
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}"/>
Results
<result dept="WMN" number="557"/>
<result dept="ACC" number="563"/>
<result dept="ACC" number="443"/>
<result dept="MEN" number="784"/>

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 24-3.

Query
for $item in doc("order.xml")//item,
    $product in doc("catalog2.xml")//product
where $item/@num = $product/number
return <item num="{$item/@num}"
             name="{$product/name}"
             quan="{$item/@quantity}"/>
Results
<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, $product/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,
    $product in doc("catalog2.xml")//product[number = $item/@num]
return <item num="{$item/@num}"
             name="{$product/name}"
             quan="{$item/@quantity}"/>

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

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

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

In SQL, it is straightforward 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 does not have an explicit grouping syntax, but grouping can be achieved using FLWOR expressions and the distinct-values function. Example 24-4 is comparable to the SQL example.

Query
for $d in distinct-values(doc("catalog2.xml")//product/@dept)
return
  <result dept="{$d}"
          count="{count(doc("catalog2.xml")//product[@dept = $d])}"/>
Results
<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 Chapter 7.