Using Joins in XQuery
In SQL, one of the most powerful ways to process data stored in Relational Data Base Management Systems (RDBMS) is to join
data from multiple tables. A join operation usually combines data from multiple tables. With FLWR expressions you can use XQuery to perform similar functions on multiple documents.
In the last example, two for
clauses were nested in the return clause of the outer FLWR expression. In this case, the tuples of variable bindings are drawn from the Cartesian product of the sequences returned by the expressions in all the for clauses. The ordering of the sequences from which they were formed, from left to right, governs the ordering of the tuples . For example, the preceding query returned only those items from items.xml
matched an itemno
. Conventionally, this type of relationship is called an inne
An outer join
is a join that preserves information from one or more of the participating documents, including elements that have no matching element in the other documents. Consider the following query, which lists all customers as well as the purchase order id
for those customers who have placed orders.
# see XQuery36.ixq in samples.zip
for $u in document("data/customers.xml")//customer
for $p in document("data/PO.xml")//po
where $u/custno = $p//custno
The query returns the following result:
<name> Joe Anderson </name>
<name> Andy Shaperd </name>
<name> Amanda Johnson </name>
<name> Bill Murphy </name>
You can see that the query result lists all
the customers, regardless of whether or not they have placed a po. Conventionally, a join that selects all the items from the left side of the join is called a left outer join
At this point, you should be able to see that XQuery provides very sophiscated mechanism to process and transform data in XML documents through FLWR expressions, built-in functions, user defined functions, and conditional operations.