CSE 636 Data Integration

CSE 636Data Integration XML Query Languages XQuery Fall 2006

XQuery • http://www.w3.org/TR/xquery/ (11/05) • Functional Programming Language • Operates on XML Sources • Returns XML

XQuery Components • XQuery is composed of • Path expressions • Element constructors • FLWOR expressions • … and more …

Path Expressions doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER CUSTOMER_ORDERS Evaluate expression by collecting all elements which satisfy the path CUSTOMER EMAIL sw@nah.com NAME Sue CUSTOMER CUSTOMER ORDER NAME Tom EMAIL td@mbi.com NO 1897 CARRIER UPS ITEM ITEM CUSTOMER ORDER ORDER NAME Ann EMAIL ag@tii.org SKU P5 QTY 1 SKU C5 QTY 2 NO 1861 NO 1878 CARRIER FEDEX ITEM CARRIER UPS ITEM SKU C5 QTY 1 SKU B7 QTY 2

Element Construction ORDER ORDER ORDER NO 1861 NO 1878 NO 1897 CARRIER FEDEX ITEM CARRIER UPS ITEM CARRIER UPS ITEM ITEM SKU C5 QTY 1 SKU B7 QTY 2 SKU P5 QTY 1 SKU C5 QTY 2 <ORDERS> { doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER } </ORDERS> A complete, executable query returning the ORDERS tree 1. Evaluate expression inside { ... } ORDERS 2. Connect into tree

Introduction to for Expression ORDERS ORDER ORDER ORDER NO 1861 NO 1878 NO 1897 CARRIER FEDEX ITEM CARRIER UPS ITEM CARRIER UPS ITEM ITEM SKU C5 QTY 1 SKU B7 QTY 2 SKU P5 QTY 1 SKU C5 QTY 2 Our path query … <ORDERS> { doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER } </ORDERS> … can be rewritten using a for expression: <ORDERS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER return $order } </ORDERS>

Topics • For-Let-Where-Order by-Return Expressions • Type Conversions • Variable Bindings • Joins • Nested Queries • Boolean Expressions • Conditionals • Aggregations • Missing Data in Joins and Nested Queries • Advanced Example • Sequences • Query Prolog

Example with where ORDERS ORDER ORDER ORDER NO 1861 NO 1878 NO 1897 CARRIER FEDEX ITEM CARRIER UPS ITEM CARRIER UPS ITEM ITEM SKU C5 QTY 1 SKU B7 QTY 2 SKU P5 QTY 1 SKU C5 QTY 2 We take our previous query and add a where clause: <ORDERS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER where $order/CARRIER = "UPS" return $order } </ORDERS> The output is the same as in the previous example, except non-UPS carriers are removed.

FLWOR Expressions: The for Clause CUSTOMER_ORDERS CUSTOMER CUSTOMER EMAIL sw@nah.com CUSTOMER NAME Sue ORDER NAME Tom EMAIL td@mbi.com NO 1897 CARRIER UPS ITEM ITEM CUSTOMER ORDER ORDER NAME Ann EMAIL ag@tii.org SKU P5 QTY 1 SKU C5 QTY 2 NO 1861 NO 1878 CARRIER FEDEX ITEM CARRIER UPS ITEM SKU C5 QTY 1 SKU B7 QTY 2 <ORDERS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER where $order/CARRIER = "UPS" return $order } </ORDERS> The for variable ranges over result of in expression

FLWOR Expressions: The where Clause CUSTOMER_ORDERS CUSTOMER CUSTOMER EMAIL sw@nah.com CUSTOMER NAME Sue ORDER NAME Tom EMAIL td@mbi.com NO 1897 CARRIER UPS ITEM ITEM CUSTOMER ORDER ORDER NAME Ann EMAIL ag@tii.org SKU P5 QTY 1 SKU C5 QTY 2 NO 1861 NO 1878 CARRIER FEDEX ITEM CARRIER UPS ITEM SKU C5 QTY 1 SKU B7 QTY 2 <ORDERS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER where $order/CARRIER = "UPS" return $order } </ORDERS> Selects only orders with UPS as the carrier

FLWOR Expressions: The return Clause ORDER ORDER NO 1897 CARRIER UPS ITEM ITEM NO 1878 CARRIER UPS ITEM SKU P5 QTY 1 SKU C5 QTY 2 SKU B7 QTY 2 <ORDERS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER where $order/CARRIER = "UPS" return $order } </ORDERS> Every $order that qualified is added to the return list:

FLWOR Expressions: Final Result ORDERS ORDER ORDER NO 1897 CARRIER UPS ITEM ITEM NO 1878 CARRIER UPS ITEM SKU P5 QTY 1 SKU C5 QTY 2 SKU B7 QTY 2 <ORDERS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER where $order/CARRIER = "UPS" return $order } </ORDERS> The list coming from the FLWOR expression … … is constructed into the ORDERS element to complete the example.

Example with Element Construction ORDERS ID 1897 ID 1878 <ORDERS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER where $order/CARRIER = "UPS" return <ID> { data($order/NO) } </ID> } </ORDERS> Here, the return statement constructs elements from values • The “data” function returns the value of an element • The return statement also contains tags • The next slide illustrates how the following result is created:

Return – Element Construction ORDERS ID 1878 ID 1897 ORDER ORDER NO 1878 NO 1897 CARRIER UPS ITEM CARRIER UPS ITEM ITEM SKU B7 QTY 2 SKU P5 QTY 1 SKU C5 QTY 2 <ORDERS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER where $order/CARRIER = "UPS" return <ID> { data($order/NO) } </ID> } </ORDERS> 4. Connect into tree 3. New element construction 2. Path selection 1. Bring in selected items as before

FLWOR Expressions: The let Clause ORDERS ID 1897 ID 1878 <ORDERS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER where $order/CARRIER = "UPS" return <ID> { data($order/NO) } </ID> } </ORDERS> Our previous example can be rewritten using extra variable bindings to improve clarity: <ORDERS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER let $carrier := $order/CARRIER let $id := data($order/NO) where $carrier = "UPS" return <ID> { $id } </ID> } </ORDERS>

FLWOR Expressions: The order by Clause CUSTOMERS CUSTOMER CUSTOMER CUSTOMER NAME Ann NAME Sue NAME Tom For this example, we prepare a list of customers sorted by customer name <CUSTOMERS> { for $customer in doc("co")/CUSTOMER_ORDERS/CUSTOMER let $name := $customer/NAME order by $customer/NAME ascending return <CUSTOMER> {$customer/NAME} </CUSTOMER> } </CUSTOMERS>

Type Conversions • In the context of functions and operators, values are automatically extracted from elements: <ORDERS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER where $order/CARRIER = "UPS" return <ID> { concat("ORDER-", $order/NO) } </ID> } </ORDERS>

Type Conversions • $order/NO binds to an element • concat(…) requires a string • Value of the element is automatically extracted • Same happens to lists containing a single element or value

Type Conversions All other cases result in errors <ORDERS> { <ID> { concat("ORDER-", doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER/NO) } </ID> } </ORDERS> • Path expression above binds to lists • Cannot extract a value from a list of many items!

Type Conversions • The data() function can be used to explicitly extract the value: <ORDERS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER where $order/CARRIER = "UPS" return <ID> { concat("ORDER-", data($order/NO)) } </ID> } </ORDERS>

Type Conversions • Automatic extraction of values does not occur in element construction • In that case, the data() function is required: <ORDERS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER where $order/CARRIER = "UPS" return <ID> { data($order/NO) } </ID> } </ORDERS>

For-Let-Where-Order By-Return (FLWOR) for $var1 inexpr returnexpr let $var2 :=expr whereexpr order byexpr return clause is executed for each remaining tuple, generating a list of trees where clause applies a predicate, eliminating some of the tuples order by clause imposes an order on the remaining tuples for and let clauses generate a list of tuples of variable bindings, preserving input order Let’s take a more in-depth look at the variable bindings in the query developed previously <ORDERS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER let $carrier := $order/CARRIER let $id := data($order/NO) where $carrier = "UPS" return <ID> { $id } </ID> } </ORDERS>

FLWOR Variable Bindings for/let where return result $order $carrier $id ORDER CARRIER FEDEX NO 1861 CARRIER FEDEX ITEM NO 1861 QTY 1 SKU C5 ORDERS ORDER CARRIER UPS NO 1878 CARRIER UPS ITEM ID 1878 ID 1878 ID 1897 YES 1878 QTY 2 SKU B7 ORDER NO 1897 CARRIER UPS ITEM ITEM CARRIER UPS ID 1897 YES 1897 QTY 1 QTY 1 SKU P5 SKU C5 <ORDERS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER let $carrier := $order/CARRIER, $id := data($order/NO) where $carrier = "UPS" return <ID> { $id } </ID> } </ORDERS>

for vs. let for • Binds node variables iteration for$xin expr • binds $x to each element in the list expr let • Binds collection variables one value let$x:= expr • binds $x to the entire list expr • Useful for common subexpressions and for aggregations

for vs. let for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER return <result> { $order } </result> Returns: <result> <ORDER>…</ORDER></result> <result> <ORDER>…</ORDER></result> <result> <ORDER>…</ORDER></result> … let $order := doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER return <result> { $order } </result> Returns: <result> <ORDER>…</ORDER> <ORDER>…</ORDER> <ORDER>…</ORDER> … </result>

for vs. let <POPULAR_ITEMS> { for $sku indistinct-values(doc(“co")//ITEM/SKU) let $items := doc(“co")//ORDER/ITEM[SKU = $sku] let $qtyTotal := sum($items/QTY) where $qtyTotal > 1 return <ITEM> { $sku } </ITEM> } </POPULAR_ITEMS> • distinct-values • a function that eliminates duplicate values • can be applied to simple elements and atomic values • sum • a (aggregate) function that returns the sum of integers

for vs. let <POPULAR_ITEMS> { for $sku indistinct-values(doc(“co")//ITEM/SKU) let $items := doc(“co")//ORDER/ITEM[SKU = $sku] let $qtyTotal := sum($items/QTY) where $qtyTotal > 1 return <ITEM> { $sku } </ITEM> } </POPULAR_ITEMS> for/let where return result $qtyTotal $items $sku ITEM QTY 1 SKU C5 ITEM C5 YES C5 3 ITEM POPULAR_ITEMS QTY 2 SKU C5 ITEM ITEM B7 ITEM C5 ITEM B7 YES B7 2 QTY 2 SKU B7 ITEM NO P5 1 SKU P5 QTY 1

for vs. let for/let where return $qtyTotal $items $avgQty ITEM NO 1.5 1 QTY 1 SKU C5 ITEM ITEM YES 1.5 2 QTY 2 QTY 2 SKU C5 SKU C5 ITEM ITEM YES 1.5 2 QTY 2 QTY 2 SKU B7 SKU B7 ITEM NO 1.5 1 SKU P5 QTY 1 Find items whose quantity is larger than average: let $avgQty := avg(doc(“co”)//ITEM/QTY) for $item in doc(“co”)//ITEM where $item/QTY > $avgQty return $item let $avgQty 1.5

Joins • Joins are expressed using a FLWOR with two loop variables • two for clauses • A where condition specifies how the loop variables relate

Join Example ORDER ORDER ORDER NO 1861 NO 1878 NO 1897 CARRIER FEDEX ITEM CARRIER UPS ITEM CARRIER UPS ITEM ITEM SKU C5 QTY 1 SKU B7 QTY 2 SKU P5 QTY 1 SKU C5 QTY 2 SHIPPER SHIPPER PICKUP 2PM PICKUP 5PM NAME FEDEX NAME UPS ORDERS ORDER ORDER ORDER ID 1861 ID 1878 ID 1897 DEADLINE 2PM DEADLINE 5PM DEADLINE 5PM Combine orders… … with shipper info … … to produce order deadlines

Join Example Query <ORDERS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER for $shipper in doc("s")/SHIPPERS/SHIPPER let $id := data($order/NO) let $time := data($shipper/PICKUP) where $order/CARRIER = $shipper/NAME return <ORDER> <ID>{$id}</ID> <DEADLINE>{$time}</DEADLINE> </ORDER> } </ORDERS> Uses multiple for statements to generate Cartesian product of tuples Uses where statement to filter Cartesian product

Join Conditions for/let where return $order $shipper $id $time ORDER SHIPPER ORDER YES 1861 2PM NAME FEDEX ID 1861 DEADLINE 2PM PICKUP 2PM NO 1861 CARRIER FEDEX ITEM … SHIPPER ORDER NO 1878 2PM NAME FEDEX PICKUP 2PM NO 1878 CARRIER UPS ITEM … SHIPPER ORDER NO 1897 2PM NAME FEDEX PICKUP 2PM NO 1897 CARRIER UPS ITEM ITEM … … SHIPPER ORDER NO 1861 5PM NAME UPS PICKUP 5PM NO 1861 CARRIER FEDEX ITEM … SHIPPER ORDER ORDER YES 1878 5PM NAME UPS PICKUP 5PM ID 1878 DEADLINE 5PM NO 1878 CARRIER UPS ITEM … SHIPPER ORDER ORDER YES 1897 5PM NAME UPS PICKUP 5PM ID 1897 DEADLINE 5PM NO 1897 CARRIER UPS ITEM ITEM … …

Condensed Join Table for/let return $order $shipper $id $time ORDER SHIPPER ORDER 1861 2PM NAME FEDEX ID 1861 DEADLINE 2PM PICKUP 2PM NO 1861 CARRIER FEDEX ITEM … SHIPPER ORDER ORDER 1878 5PM NAME UPS PICKUP 5PM ID 1878 DEADLINE 5PM NO 1878 CARRIER UPS ITEM … SHIPPER ORDER ORDER 1897 5PM NAME UPS PICKUP 5PM ID 1897 DEADLINE 5PM NO 1897 CARRIER UPS ITEM ITEM … … <ORDERS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER, $shipper in doc("s")/SHIPPERS/SHIPPER let $id := data($order/NO), $time := data($shipper/PICKUP) where $order/CARRIER = $shipper/NAME return <ORDER> <ID>{$id}</ID> <DEADLINE>{$time}</DEADLINE> </ORDER> } </ORDERS> In future examples, non-joined rows are removed, as are join where conditions:

Nested Queries • Nested queries produce hierarchical results • An outer FLWOR loop contains an inner FLWOR loop • Typically, a where condition in the inner FLWOR specifies how the loops relate

Nested Query Example ORDER ORDER ORDER … with orders … NO 1861 NO 1878 NO 1897 CARRIER FEDEX ITEM CARRIER UPS ITEM CARRIER UPS ITEM ITEM SKU C5 QTY 1 SKU B7 QTY 2 SKU P5 QTY 1 SKU C5 QTY 2 SHIPPER_ORDERS … to produce orders for each shipper SHIPPER SHIPPER ORDER 1861 ORDER 1878 ORDER 1897 NAME FEDEX NAME UPS Combine shippers… SHIPPER SHIPPER NAME FEDEX PICKUP 2PM NAME UPS PICKUP 5PM

Nested Query <SHIPPER_ORDERS> { for $shipper in doc("s")/SHIPPERS/SHIPPER let $name := $shipper/NAME return <SHIPPER> { $name } { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER let $id := data($order/NO) where $name = $order/CARRIER return <ORDER> { $id } </ORDER> } </SHIPPER> } </SHIPPER_ORDERS> • Outer loop binds $shipper and $name variables • For each $shipper, $name pair, inner loop binds $order and $id variables • Inner where clause removes $order, $id pairs that don’t match outer element • Inner loop constructs elements from inner variables • Outer loop constructs elements from outer variables and from elements constructed in inner loop

Join Conditions OUTER LOOP INNER LOOP OUTER LOOP where return return $shipper $name $order $id ORDER ORDER 1861 YES 1861 NO 1861 CARRIER FEDEX ITEM … SHIPPER ORDER SHIPPER NAME FEDEX NO 1878 NAME FEDEX PICKUP 2PM NO 1878 CARRIER UPS ITEM NAME FEDEX ORDER 1861 … ORDER NO 1897 NO 1897 CARRIER UPS ITEM ITEM … … ORDER NO 1861 NO 1861 CARRIER FEDEX ITEM … SHIPPER ORDER SHIPPER NAME UPS ORDER 1878 YES 1878 NAME UPS PICKUP 5PM NO 1878 CARRIER UPS ITEM NAME UPS ORDER 1878 ORDER 1897 … ORDER ORDER 1897 YES 1897 NO 1897 CARRIER UPS ITEM ITEM … …

Condensed Nested Query Table <SHIPPER_ORDERS> { for $shipper in doc("s")/SHIPPERS/SHIPPER let $name := $shipper/NAME return <SHIPPER> { $name } { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER let $id := data($order/NO) where $name = $order/CARRIER return <ORDER> { $id } </ORDER> } </SHIPPER> } </SHIPPER_ORDERS> In future examples, non-matched inner rows are removed, as are where conditions: OUTER LOOP INNER LOOP OUTER LOOP where return return $shipper $name $order $id ORDER SHIPPER SHIPPER ORDER 1861 YES NAME FEDEX 1861 NO 1861 CARRIER FEDEX ITEM NAME FEDEX PICKUP 2PM NAME FEDEX ORDER 1861 … ORDER ORDER 1878 YES 1878 NO 1878 CARRIER UPS ITEM SHIPPER SHIPPER NAME UPS … NAME UPS PICKUP 5PM NAME UPS ORDER 1878 ORDER 1897 ORDER ORDER 1897 YES 1897 NO 1897 CARRIER UPS ITEM ITEM … …

Boolean Expressions • In this section we examine various types of Boolean expressions that may appear in WHERE clauses

Functions in Boolean Expressions for/let where return result $order $id $lc ORDER NO 1861 CARRIER FEDEX ITEM 1 NO 1861 QTY 1 SKU C5 ORDERS_IDS ORDER NO 1878 CARRIER UPS ITEM 1 NO 1878 ID 1897 QTY 2 SKU B7 ORDER NO 1897 CARRIER UPS ITEM ITEM ID 1897 YES 2 1897 QTY 1 QTY 1 SKU P5 SKU C5 <ORDER_IDS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER let $id := data($order/NO), $lc := count($order/ITEM) where $lc > 1 return <ID> { $id } </ID> } </ORDER_IDS>

Disjunctions for/let where return result $order $id $lc ORDER NO 1861 CARRIER FEDEX ITEM ID 1861 1 YES 1861 QTY 1 SKU C5 ORDERS_IDS ORDER NO 1878 CARRIER UPS ITEM 1 NO 1878 ID 1861 ID 1897 QTY 2 SKU B7 ORDER NO 1897 CARRIER UPS ITEM ITEM ID 1897 YES 2 1897 QTY 1 QTY 1 SKU P5 SKU C5 <ORDER_IDS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER let $id := data($order/NO), $lc := count($order/ITEM ) where $lc > 1 or $order/CARRIER = "FEDEX" return <ID> { $id } </ID> } </ORDER_IDS>

Existential Quantification for/let where return result $order $id $sku ORDER SKU C5 NO 1861 CARRIER FEDEX ITEM ID 1861 YES 1861 QTY 1 SKU C5 ORDERS_IDS ORDER SKU B7 NO 1878 CARRIER UPS ITEM NO 1878 ID 1861 ID 1897 QTY 2 SKU B7 ORDER SKU P5 NO 1897 CARRIER UPS ITEM ITEM ID 1897 YES 1897 SKU C5 QTY 1 QTY 1 SKU P5 SKU C5 <ORDER_IDS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER let $id := data($order/NO) wheresome $sku in $order/ITEM/SKU satisfies $sku = "C5" return <ID> { $id } </ID> } </ORDER_IDS>

Universal Quantification for/let where return result $order $id $sku ORDER SKU C5 NO 1861 CARRIER FEDEX ITEM ID 1861 YES 1861 QTY 1 SKU C5 ORDERS_IDS ORDER SKU B7 NO 1878 CARRIER UPS ITEM NO 1878 ID 1861 QTY 2 SKU B7 ORDER SKU P5 NO 1897 CARRIER UPS ITEM ITEM NO 1897 SKU C5 QTY 1 QTY 1 SKU P5 SKU C5 <ORDER_IDS> { for $order in doc("co")/CUSTOMER_ORDERS/CUSTOMER/ORDER let $id := data($order/NO) whereevery $sku in $order/ITEM/SKU satisfies $sku = "C5" return <ID> { $id } </ID> } </ORDER_IDS>

Conditionals Example Tree CUSTOMER CUSTOMER CUSTOMER NAME Ann NAME Tom NAME Sue … with member info … MEMBER MEMBER MEMBER STATUS GOLD STATUS SILVER STATUS GOLD NAME Tom NAME Bob NAME Sue … to add MEMBER tag to customer data CUSTOMERS CUSTOMER CUSTOMER CUSTOMER NAME Ann NAME Tom NAME Sue MEMBER NO MEMBER YES MEMBER YES Combine customers …

CSE 636 Data Integration