Basic Principles, Lesson 4
XQuery FLWOR Expression JOINs

Lesson Scenario

All the lessons in this Primer are based on a real-world scenario.

XYZ.COM has used XQuery path expressions and FLWOR expressions to query the retailer purchase orders to extract the order quantities for each product made by a specific manufacturer. Now, XYZ.COM is nearing the point where they can send a web message to the product manufacturers; however they must also verify each order with its own product inventory to calculate the correct amount to order from the manufacturers.

In Lesson 3, we covered XQuery FLWOR expressions to query the retail purchase order and construct a portion of the web message we need. In this lesson, we extend the FLWOR expression to query two data sources - the retailer purchase order and the product inventory database.

XQuery FLWOR Expression JOINs

Queries in XQuery often combine information for one or more data sources and create a result based on them. The data sources may contain any data that is represented by XML, including native XML or XML views of data such as relational data.

Integrating two or more data sources together is often described as a 'JOIN', and is similar to the SQL concept of joining two or more tables together to obtain a result. For XYZ.COM, we need to write an XQuery expression across both the inventory database and the retailer purchase order.

In previous lessons we have used the retailer's purchase order which is immediately accessible by XQuery since it is an XML document. To access XYZ.COM's inventory database, we use XQuery's ability to query an XML view of data - in this case we are using the SQL/XML mapping to establish the XML view of the database.

In the XYZ.COM relational database, the product inventory for the "ACME" manufacturer looks like this.

The 'ACME_PRODUCT_TABLE' is stored in a relational database hosted by XYZ.COM. It records the current inventory of each product in the 'Cur_Quantity' column, and contains additional columns 'Min_Quantity' and 'Max_Quantity' that describe the minimum and maximum product quantities that ensure XYZ.COM always has sufficient inventory to deliver product on time.

Consider the following XQuery FLWOR expression:

<manufacturer>
    {
    for $entry in doc("po.xml")/invoicecollection/invoice/entries/entry
    where $entry/product/@maker = "ACME" and
        $entry/@quantity <= 
            collection('SCOTT.ACME_PRODUCT_TABLE')/row/Cur_Quantity
        return
        <order>
            <quantity>
                {data($entry/@quantity)}
            </quantity>
            <product>
                {data($entry/product/@prod_name)}
            </product>
        </order>
    }
</manufacturer>

This XQuery FLWOR expression binds the variable $entry to each entry in the retail purchase order document, po.xml.

The where clause tests two conditions:

• If each product tuple is made by the manufacturer "ACME".
• And if the quantity of the retailer purchase order is less than or equal to the current inventory of each product tuple made by "ACME". We use the collection() function that returns a collection (a sequences of nodes that is associated with a URI). In this case the URI refers to the XYZ.COM relational database.

The return clause constructs a subsequent order only if there is sent to the manufacturer.

The XML produced looks like this:

<manufacturer>
    <order>
        <quantity>2</quantity>
        <product>atomic hammer</product>
    </order>
    <order>
        <quantity>1</quantity>
        <product>power springs</product>
    </order>
</manufacturer>

XYZ.COM can now send this XML as part of a purchase order web message to the ACME Web Service end point to process the order. By using a simple join, we have successfully verified the inventory of the XYZ.COM by checking the retail purchase order against the relational database product quantities.

Summary

XQuery FLWOR expressions often combine information from one or more data sources that may be native XML or an XML view of data such as relational databases.