Native XML Database

If you are familiar with SQL you have probably seen quantified SQL predicates that compare a single value with a set of values. For example, the following SQL query returns all rows from table1 where col1 is greater than all values in col2 of table2.

SELECT *
FROM table1
WHERE col1  > ALL (SELECT col2 FROM table2)

And if you replace the keyword ALL with the keyword SOME, then the query above returns all rows from table1 where col1 is greater than at least one of the values in col2 of table2.

These comparisons are also known as universal quantification (“ALL”) and existential quantification (“SOME”), respectively.

Similar quantified comparisons are possible in XQuery too, but the syntax is slightly different. In XQuery you have the keywords “every … satisfies ….” for  universal quantification and “some … satisfies …” for existential quantification. Let’s use the following table and two simple sample documents to explore how they work:

create table customer(doc XML);

insert into customer values(‘
<customer id=”1″>
    <phone type=”cell”>408-516-4963</phone>
    <phone type=”cell”>408-087-1234</phone>
    <phone type=”cell”>408-111-222</phone>
</customer>
‘);

insert into customer values(‘
<customer id=”2″>
    <phone type=”home”>408-516-4963</phone>
    <phone type=”cell”>408-087-1234</phone>
    <phone type=”office”>408-111-222</phone>
</customer>
‘);

Now assume that we want to find customers where all their phone numbers are of type “cell”. We can code a corresponding query in XQuery or in SQL/XML notation as follows:

xquery
for $c in db2-fn:xmlcolumn(“CUSTOMER.DOC”)/customer
where every $p in $c/phone satisfies $p/@type = “cell”
return $c;

select *
from customer
where xmlexists (‘$DOC/customer[ every $p in ./phone satisfies $p/@type = "cell"]‘);

Both queries return just the first of the two customer documents. The second document is not selected because not all of its phone elements have a @type attribute with the value “cell”.

The quantified expression every $p in $c/phone satisfies $p/@type = “cell” means that $p iterates over all the items in the sequence $c/phone,  and for each such $p the predicates $p/@type = “cell” must be true.

If we want to return all documents where at least one of the phone number is of type “cell” (existential quantification!), then we can simply replace the keyword “every” with the keyword “some”, such as in the following query:

select *
from customer
where xmlexists (‘$DOC/customer[ some $p in ./phone satisfies $p/@type = "cell"]‘);

This query returns both our sample documents, because both documents contain at least one phone element where the @type attribute has the value “cell”. Since XPath uses existential quantification by default, you can often omit the “some … satisfies …” and obtain the same result with an ordinary path expression, like this:

select *
from customer
where xmlexists (‘$DOC/customer[phone/@type = "cell"]‘);

For more information, use the following resources.

DB2 Information Center – Quantified Expressions:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.xml.doc/doc/xqrquanexp.html

XQuery specification of quantified expressions:
http://www.w3.org/TR/xquery/#id-quantified-expressions