FLWOR

4.1

4. FLWOR XQuery's version of SQL Select

SKILLBUILDERS

© 2005-2006 SkillBuilders, Inc. XQuery Training

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.2

4.2

Query Body: FLWOR ¾ Just as with SQL select, with XQuery you state what you want to retrieve rather than how you are going to retrieve it ¾ XQuery's feature closest to SQL select is FLWOR expression ¾

Uses keywords for, let, where, order by, and return

¾

Pronounced "flower"

¾ An expression becomes a FLWOR expression as soon as either for or let is used ¾ See example in notes, below and commentary XQuery Training

© 2005-2006 SkillBuilders, Inc.

The following example illustrates the various FLWOR clauses: (1) { (2) for $i in doc("report.xml")//customer (3) let $totalUnits := sum($i//@quantity) (4) where sum($i//@total) < 1000 (5) order by $i/name (6) return (7) (8) {data($i/name)} (9) (10) } (11)

(See next page for output of this query) (1), (11) The opening and closing tags will be sent to the output. They will contain the return value of the XQuery (1), (10) The entire XQuery body is separated from the enclosing tags by curly braces. You would not need these outermost curly braces if the opening and closing tags were not present. (2) The doc() function opens an external XML file and returns the document node. doc("reports.xml")//customer creates a sequence of all the document's descendent customer nodes. The for … clause iterates through each of the customer nodes, assigning each one in turn to the variable $i. Continued . . .

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.3

(3) The let clause assigns a value to the variable named $totalUnits. The value assigned is the sum of all of the quantity attributes found in all of the current customer's descendents. (4) The where clause filters out some of the instances of the nodes selected by the for clause. In this case, only those in which the sum of all of the total attributes found in all of the current customer node's descendents amounts to less than 1000 are retained. The others are eliminated and not processed further. (5) The order by clause specifies what will be used as the sort key to order the selected nodes that are returned. In this case it's the value of the customer's nested name tag. (6)-(9) The return clause specifies what the output of this query will be (aside from the root element ). (7) For each customer iteration (established by the for clause) an opening tag will be generated. Each name tag will have two attributes, count and totalUnits. The value assigned to the count attribute is the total number of the current customer's descendent item elements, using the built-in count() function. The value assigned to the totalUnits attribute is the current value of the $totalUnits variable set in line (3). (8) The text content of the tag is the text value of the current customer's child element. The value is extracted using the data( ) function. The string( ) function could also have been used. Notice that in the return clause XQuery variables are separated from text output by curly braces. ------------------------------------------------------------------------------------------------------------------Here is the output from the query on preceding page: Mad Hatter

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.4

4.4

FLWOR: for ¾ for clause designates variable that will be bound to each item in a sequence and effectively iterates through each of the values setting the variable to each value in turn ¾ ¾

There can be multiple for clauses in an XQuery To get position of current item in iteration use at followed by variable before in keyword ¾

See example in notes below

¾ Presence of for clause (or let clause -- see following page) creates FLWOR expression ¾

return statement is required

XQuery Training

© 2005-2006 SkillBuilders, Inc.

The following example shows how to bind the current iteration's position to a variable. It also illustrates the use of multiple for clauses: for $i at $p in doc("report.xml")//customer for $j at $q in $i//item return (' ', concat(string($p), '.'), string($i/name), concat(concat('(', string($q)), ')'), string($j)) The first for binds the value of the current customer element to variable $i and it binds the position of the current customer element to the variable $p. The second for clause binds value of the current item element (within the current customer) to the variable $j, and it binds the position of the current item element (within the current customer) to the variable $q. The at operator begins counting with 1. The output produced by the above query is as follows: 1. Mad Hatter (1) Economy widgets 1. Mad Hatter (2) Small widgets 1. Mad Hatter (3) Large gadgets 1. Mad Hatter (4) Discount gidgets 2. Alice Liddle (1) Economy widgets 2. Alice Liddle (2) Small widgets 2. Alice Liddle (3) Large gadgets 2. Alice Liddle (4) Discount gidgets 2. Alice Liddle (5) Great big gadgets 3. Aloysius Swenson (1) Marbles 3. Aloysius Swenson (2) Ball bearings 3. Aloysius Swenson (3) Baseballs 4. Homer Twombly (1) Cabbage 4. Homer Twombly (2) Atis 4. Homer Twombly (3) Coconut

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.5

4.5

FLWOR: let... ¾ let binds a variable to a value or a sequence ¾ Differs from for in that it does not iterate through set; entire set bound to variable ¾ Assignment operator: := ¾ Examples: let let $x $x := := 123 123 let $y := let $y := concat("xyz", concat("xyz", $x) $x) return $y return $y

¾

In this example $y has value "xyz123"

¾

In this example $z is bound to the entire set of customer nodes let let $z $z := := doc("mydoc.xml")//customer doc("mydoc.xml")//customer

XQuery Training

© 2005-2006 SkillBuilders, Inc.

Remember that the use of any for clause or the use of any let clause automatically creates a FLWOR statement which must be terminated with a return clause.

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.6

4.6

...FLWOR: let ¾ for and let clauses can appear in any order relative to each other ¾

See example in notes, below

¾ You can have a FLWOR expression with just let and no for, with just for and no let, or with any combination of both for and let ¾ In any case, if for and/or let is used, expression must use return statement

XQuery Training

© 2005-2006 SkillBuilders, Inc.

The following example illustrates how the for and let clauses can appear in any order relative to each other: let $x := "hello" for $y in doc("report.xml")//customer let $z := $y/@type for $a in $y//item return (' ', $x, ' ', string($y/name), ' ', string($z), ' ', string($a)) Output: hello hello hello hello hello hello hello hello hello hello hello hello hello hello hello

Mad Hatter VIP Economy widgets Mad Hatter VIP Small widgets Mad Hatter VIP Large gadgets Mad Hatter VIP Discount gidgets Alice Liddle normal Economy widgets Alice Liddle normal Small widgets Alice Liddle normal Large gadgets Alice Liddle normal Discount gidgets Alice Liddle normal Great big gadgets Aloysius Swenson normal Marbles Aloysius Swenson normal Ball bearings Aloysius Swenson normal Baseballs Homer Twombly normal Cabbage Homer Twombly normal Atis Homer Twombly normal Coconut

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.7

4.7

FLWOR: where ¾

where clause establishes a filtering mechanism by setting a condition that must be met by each item or that item is eliminated and not passed on to return clause

¾

For example: for for $z $z in in doc("report.xml")//customer doc("report.xml")//customer where where $z/@type $z/@type == 'VIP' 'VIP' return return $z/name $z/name

¾ ¾

Previous example returns only those customer nodes that have the type attribute set to "VIP" where clause is optional

XQuery Training

© 2005-2006 SkillBuilders, Inc.

Often we can replace the use of the where clause with a properly constructed XPath expression. For example, the following will produce the same results as the FLWOR query with the where clause, in the slide: doc("report.xml")//customer[@type = 'VIP']/name Although the results are the same there may be optimization advantages using one over the other.

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.8

4.8

Quantified Expressions ¾ some/every ... in ... satisfies ¾ Analogous to exists in SQL ¾ Makes writing some conditions more readable. For example: where where some some $emp $emp in in $dept $dept satisfies satisfies $emp/age $emp/age >> 65 65 where where every every $emp $emp in in $dept $dept satisfies satisfies $emp/age $emp/age >> 65 65

XQuery Training

© 2005-2006 SkillBuilders, Inc.

Here are two examples using some/every. These queries use the file report.xml that is distributed with the class files. Print a list of customers who have orders in which the quantity of each order is at least 2: for $r in doc("report.xml")//customer where every $x in $r//@quantity satisfies $x > 1 return $r//name Result :

Alice Liddle Aloysius Swenson ------------------------------------------------------------------------------------------------------------------

Print a list of customers who have at least one order in which the quantity is 1: for $r in doc("report.xml")//customer where some $x in $r//@quantity satisfies $x = 1 return $r//name Result :

Mad Hatter Homer Twombly

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.9

4.9

FLWOR: order by ¾ order by controls the order in which elements are returned by specifying sort field(s) ¾ Multiple sort fields can be specified to break ties ¾

Separate items by comma

¾ Each sort field can optionally be followed by keyword ascending (default) or descending ¾ Example: order order by by $a/name, $a/name, $a/@balance $a/@balance descending descending

XQuery Training

© 2005-2006 SkillBuilders, Inc.

Sometimes, especially when sorting foreign languages, we may need to specify a particular collation rule. The collation keyword allows for this, as shown in the example below: order by $c/name collation "zh-TW" This uses the standard two-letter lower-case ISO-639 language code optionally followed by "-" and standard two-letter upper case ISO-3166 country code. ISO-639 language codes can be found at: ¾ http://www.loc.gov/standards/iso639-2/englangn.html#st

ISO-3166 country codes can be found at: ¾ http://www.iso.org/iso/en/prods-services/iso3166ma/02iso-3166-code-lists/listen1.html

When ordering by a numeric field you will often have to convert the field to a number first. Use one of the type constructors -- xs:integer(), xs:decimal, etc. -- or use the cast as ... expression. Note that while you do not have to declare the "xs:" prefix in XQuery -- the "xs:" prefix is predefined in XQuery -- you must use it when using these XML Schema data constructors.

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.10

4.10

stable ¾ Keyword "stable" guarantees sort order ties will preserve original document order ¾ Example: for for $i $i stable stable return return

in in doc("report.xml")//item doc("report.xml")//item order order by by $i/@quantity $i/@quantity cast cast as as xs:integer xs:integer $i $i

¾ If "stable" is omitted, in the event of ties the resulting order is implementation dependent XQuery Training

© 2005-2006 SkillBuilders, Inc.

Note that the above query could also have been written as follows: for $i in doc("report.xml")//item stable order by xs:integer($i/@quantity) return $i The reason for the cast to xs:integer in the above examples is that if quantity is not treated numerically "6" would follow "10" for example, whereas if treated numerically "6" of course precedes "10".

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.11

4.11

Null handling ¾ Sorting in XQuery must confront the same issue of ordering nulls in SQL ¾ The issue is how to sort a value which could be null (empty) ¾ The order by clause can specify: ¾ empty

least ¾ empty greatest

¾ empty least considers an empty value as the smallest value ¾ empty greatest is the opposite XQuery Training

© 2005-2006 SkillBuilders, Inc.

Note that this applies to data that is non-existent -- not simply a zero-length string. For example, an attribute that is completely lacking; not simply an attribute with a value of "". The following example shows use of the empty least|greatest clause: for $i in doc("reportWithEmptyQuantity.xml")//item order by xs:integer($i/@quantity) empty greatest return $i This query will sort any selected nodes that are lacking a quantity attribute by treating the value as higher than all others. Once again, note that "lacking a quantity attribute" is not the same as having a quantity attribute of "" (empty string). Note that Saxon uses "empty least" as the default if no "empty …" clause is specified. Output: Large gadgets Small widgets Great big gadgets Economy widgets Ball bearings Discount gidgets Small widgets Large gadgets Discount gidgets Marbles Cabbage Atis Baseballs Economy widgets Coconut

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.12

============================================= A digression on "null" handling in XQuery . . . ============================================= The query given in the notes on the previous page runs fine using Saxon. When run with another XQuery engine, Qizxopen, it produced the following error: *** execution error: unexpected empty sequence xs:integer($i/@quantity) When the Qizxopen engine came to the "null" or empty quantity it threw an exception because it could not cast an empty value as an integer. Saxon, as mentioned, simply returns an empty sequence (or "null") when you attempt to cast an empty sequence (or "null") as an integer. To get the same query to run in Qizxopen the query was rewritten as follows: for $i in doc("../xml/reportWithEmptyQuantities.xml")//item order by if ($i/@quantity) then xs:integer($i/@quantity) else () empty greatest return $i In the rewritten version before casting the value of the quantity attribute as an integer we test to see whether a quantity attribute exists in the current context (i.e., the current iteration of //item). If it does exist, we convert it to an integer. If not, we simply return the empty sequence (or "null" value) designated by the empty opening and closing parentheses. The above query now works in both Saxon and Qizxopen.

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.13

4.13

FLWOR: return ¾ return specifies values to be returned ¾

Value can be a single value or sequence of values

¾ Returned element node: all tags and tags of any descendent elements preserved in output ¾ Returned attribute node becomes an attribute of the enclosing tag ¾ To mix XML and XQuery expressions, such as variables, sequences, and functions enclose XQuery data within curly braces

XQuery Training

© 2005-2006 SkillBuilders, Inc.

See examples on following pages. See Appendix B for a listing of report.xml, customerInfo.xml and the other XML documents used in the examples and workshops in this course. Note that the return clause is the only required clause in a FLWOR expression. Of course, to be a FLWOR expression in the first place, there must be either a for or a let (or both). The handling of returned attribute nodes is implementation dependent. Saxon does not allow returning an attribute node without enclosing it in a tag or stripping out its string value or type value (with string(), data(), or cast as ...). Take, for example, the following query: for $i in doc("report.xml")//order return $i/@status Saxon throws the following error: SENR0001: Cannot serialize a free-standing attribute node (status) Query processing failed: Run-time errors were reported Qizxopen, on the other hand, has no problem with the same query, and returns the following: status = late status = backordered status = late status = late status = backordered Returned attribute nodes that will become attribute nodes of the enclosing tag must precede other items being returned.

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.14

============================================= XQuery examples using FLWOR ============================================= Example 1: Output each name node, as is: for $r in doc("report.xml")//name return $r --------------------------------------------Returns: Mad Hatter Alice Liddle Aloysius Swenson Homer Twombly --------------------------------------------------------------------------------Example 2: Output each node, with enclosing element. Notice that query must be enclosed in curly braces. { for $r in doc("report.xml")//name return $r } --------------------------------------------Returns: Mad Hatter Alice Liddle Aloysius Swenson Homer Twombly Continued on next page . . .

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.15

XQuery Examples Using FLWOR, continued: Example 3a: Output each node, with new tag name. Notice that when mixed with XML, XQuery output must be enclosed in curly braces. Notice also that the value of the node is stripped out before outputting by using the data() function. In this case the string() function would have accomplished the same thing. for $r in doc("report.xml")//name return {data($r)} --------------------------------------------Returns: Mad Hatter Alice Liddle Aloysius Swenson Homer Twombly --------------------------------------------------------------------------------Example 3b: Produces same result as example 3a, but uses text() function to return element's text value as a string: for $r in doc("report.xml")//name return {$r/text()} --------------------------------------------------------------------------------Example 4: Produces same results as examples 3a and 3b. Output each node, with new tag name using element constructor. We have not yet discussed element constructors. Element constructors are discussed in lesson 5 "XQuery Data." for $r in doc("report.xml")//name return element nameOfPerson {data($r)} ---------------------------------------------

Continued on next page . . .

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.16

XQuery Examples Using FLWOR, continued: Example 5a: Using an attribute node. Notice that attributes must be returned before any other data: for $r in doc("report.xml")//name return {$r/../@type} {string($r)} --------------------------------------------Returns: Mad Hatter Alice Liddle Aloysius Swenson Homer Twombly --------------------------------------------------------------------------------Example 5b: Erroneous use of attribute node. Notice what happens when attribute nodes are not returned before element text: for $r in doc("report.xml")//name return {string($r)} {$r/../@type} --------------------------------------------Saxon returns: SENR0001: Cannot serialize a free-standing attribute node (type) Query processing failed: Run-time errors were reported Qizxopen returns: *** execution error: error in constructor: attribute added after contents {$r/../@type} ---^---------------------------------------------

Continued on following page . . .

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.17

XQuery Examples Using FLWOR, continued: Example 6: Output each node, with where clause. Notice use of built-in matches() function which uses regular expressions. Here it is testing for names beginning with "A". See Appendix C for a brief discussion of regular expressions. { for $r in doc("report.xml")//name where matches($r, "^A") return $r } --------------------------------------------Returns: Alice Liddle Aloysius Swenson --------------------------------------------------------------------------------Example 7: Output each name, and order by total amount of order in descending sequence. Use computed total amount of order as attribute value. In the event of a tie in the total order value order by name descending. for $r in doc("report.xml")//name let $s := sum($r/..//order/@total) order by $s descending, $r descending return {string($r)} --------------------------------------------Returns: Alice Liddle Homer Twombly Aloysius Swenson Mad Hatter ---------------------------------------------------------------------------------

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.18

4.18

FLWOR: joins ¾ With XQuery the equivalent of SQL join can be done easily with XML documents ¾ Use two (or more) for clauses separated by commas ¾ where clause selects out rows, linking multiple documents on a commonality that they share ¾ return clause can return values from either or both documents ¾ Example of join follows listing of second XML document on following page

XQuery Training

© 2005-2006 SkillBuilders, Inc.

Somewhat analogous to an SQL union operation is the XQuery collection() function discussed in a lesson 5. See Appendix B for Listing of customerInfo.xml (to be joined with report.xml, mentioned earlier) See example query joining customerInfo.xml with report.xml on the following page.

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.19

The following query joins the two XML documentsmentioned and extracts some information from each of them: { for $a in doc("report.xml")//customer, $b in doc("customerInfo.xml")//customer let $lastFirst := concat(string($b//lastName), concat(', ', string($b//firstName))) where $a/@number = $b/@custId order by $b//lastName return {$a/@number} {$a/@type} {sum($a//@total)} {$lastFirst} {($b//city/text(), ', ', $b//state/text(), ' ', $b//zip/text()), $b//country/text()} } --------------------------------------------------------------------

Results returned by above query: 800 Hatter, Mad Mesa, AZ 80995 3490 Liddle, Alice Cletus, AR 53522 2150 Swenson, Aloysius Butte, MT 91101 2150 Twombly, Homer Tagaytay, Philippines

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.20

4.20

FLWOR: nested ¾ FLWOR statements can be nested ¾ Each iteration of one for clause, for example, can be used as basis of iteration in a second for clause ¾ FLWOR statement can also be used as contents of return clause ¾ See examples in notes on following page

XQuery Training

© 2005-2006 SkillBuilders, Inc.

See following page for examples of nested FLWOR statements.

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.21

Nested FLWOR examples: Example 1: Suppose you want a list of customers in ascending sequence by name together with each item they purchased, with the items in descending order. The following query uses each iteration of the first for clause (which iterates over name) as the basis of iteration over each item for that customer using a second for clause. This description may sound odd, but analyzing the example and the returned results should clarify the matter. (Note: ' ' is a new line character). for $x in doc("report.xml")//customer for $y in $x//item order by $x/name, $y descending return (' ', $x/name/text(), ' ', $y/text()) Returns. Notice that the names are in ascending order, and the items in descending order within the names. Alice Liddle Small widgets Alice Liddle Large gadgets Alice Liddle Great big gadgets Alice Liddle Economy widgets Alice Liddle Discount gidgets Aloysius Swenson Marbles Aloysius Swenson Baseballs Aloysius Swenson Ball bearings Homer Twombly Coconut Homer Twombly Cabbage Homer Twombly Atis Mad Hatter Small widgets Mad Hatter Large gadgets Mad Hatter Economy widgets Mad Hatter Discount gidgets Example 2: Produce the exact same results as the previous example, but use a FLWOR statement in the return clause of an outer FLWOR statement. In this case, for each name that the query returns it loops through the items belonging to that name and returns the name and the item. Again, analyze the example and the returned results to understand what is happening. for $x in doc("report.xml")//customer order by $x/name return for $y in $x//item order by $y descending return (' ', $x/name/text(), ' ', $y/text()) Returns: Results are exactly the same as example1.

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.22

4.22

Where We've Been ¾ XQuery FLWOR construct resembles SQL select ¾ Provides convenient mechanism for: ¾ ¾ ¾ ¾ ¾

iteration (for) assignment (let) filtering (where) sequencing (order by) returning data (return)

¾ Provides simple mechanism for join operation

XQuery Training

© 2005-2006 SkillBuilders, Inc.

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.23

4.23

XQuery FLWOR Workshop ¾ Use FLWOR statements to create a formatted html document

XQuery Training

© 2005-2006 SkillBuilders, Inc.

Your Objective: In this exercise you will use XQuery FLWOR statements to produce an Unfilled Orders Report as an HTML document. Your result should look more or less like the following when opened in a browser such as Internet Explorer or Mozilla:

Some pointers are given on the following page.

© 2005-2006 SkillBuilders, Inc.

V1.1

FLWOR

4.24

¾

Show results by ordered first by status, and secondarily by name

¾

A skeleton file containing the html (x-04-workshop-html-skeleton.xq) is located in the solutions directory. You can use this file as a basis to start constructing your query.

¾

The actual XQuery statements are contained within the outer enclosing html tags (, , )

¾

Remember to enclose the actual XQuery statements within braces { }

¾

Remember also to enclose XQuery expressions within braces when they are interspersed with various html tags (, )

¾

When you have produced your output open it in a browser to check the results

¾

XQuery output can be written to a file in either of two ways: ¾Use redirection: myxquery myLab4.xq > myReport.html ¾Use Saxon -o command line option: myxquery -o myReport.html myLab4.xq

© 2005-2006 SkillBuilders, Inc.

V1.1