Recall from Last Lecture: XPath bib matches a bib element * matches any element CS/INFO 330 / matches the root element /bib matches a bib element under root XQuery bib/paper matches a paper in bib bib//paper matches a paper in bib, at any depth //paper matches a paper at any depth paper|book matches a paper or a book October 23, 2005: Guest lecture by Al Demers @price matches a price attribute October 26: Continuation by JG bib/book/@price matches price attribute in book, in bib Slides courtesy of Dan Suciu, University of Washington bib/book/[@price<“55”]/author/lastname matches… 1 2 XQuery FLWR (“Flower”) Expressions • Based on Quilt, which is based on XML-QL • Uses XPath to express more complex FOR ... FOR ... queries LET... LET... WHERE... WHERE... RETURN... RETURN... 3 4 Sample Data for Queries (more FOR-WHERE-RETURN or less) <bib> <bib> <book> <publisher> Addison-Wesley </publisher> <book> <publisher> Addison-Wesley </publisher> Find all book titles published after 1995: <author> Serge Abiteboul </author> <author> Serge Abiteboul </author> <author> <first-name> Rick </first-name> <author> <first-name> Rick </first-name> <last-name> Hull </last-name> <last-name> Hull </last-name> FOR $x IN document("bib.xml")/bib/book FOR $x IN document("bib.xml")/bib/book </author> </author> <author> Victor Vianu </author> <author> Victor Vianu </author> WHERE $x/year/text() > 1995 <title> Foundations of Databases </title> WHERE $x/year/text() > 1995 <title> Foundations of Databases </title> <year> 1995 </year> <year> 1995 </year> RETURN $x/title RETURN $x/title </book> </book> <book price=“55”> <book price=“55”> <publisher> Freeman </publisher> <publisher> Freeman </publisher> Result: <author> Jeffrey D. Ullman </author> <author> Jeffrey D. Ullman </author> <title> Principles of Database and Knowledge Base Systems </title> <title> abc </title> <title> Principles of Database and Knowledge Base Systems </title> <year> 1998 </year> <year> 1998 </year> <title> def </title> </book> </book> <title> ghi </title> </bib> </bib> 5 6 1
FOR-WHERE-RETURN FOR-WHERE-RETURN Equivalently (perhaps more geekish) • Find all book titles and the year when they were published: FOR $x IN document("bib.xml")/bib/book[year/text() > 1995] /title FOR $x IN document("bib.xml")/bib/book[year/text() > 1995] /title FOR $x IN document("bib.xml")/bib/book FOR $x IN document("bib.xml")/bib/book RETURN $x RETURN $x RETURN <answer> RETURN <answer> <what>{ $x/title/text() } </what> <what>{ $x/title/text() } </what> <when>{ $x/year/text() } </when> <when>{ $x/year/text() } </when> And even shorter: </answer> </answer> document("bib.xml")/bib/book[year/text() > 1995] /title document("bib.xml")/bib/book[year/text() > 1995] /title We can construct whatever XML results we want ! 7 8 Answer FOR-WHERE-RETURN <answer> <answer> • Notice the use of “{“ and “}” <what> How to cook a Turkey </what> <what> How to cook a Turkey </what> <when> 2003 </when> <when> 2003 </when> • What is the result without them ? </answer> </answer> <answer> <answer> <what> Cooking While Watching TV </what> <what> Cooking While Watching TV </what> FOR $x IN document("bib.xml")/bib/book FOR $x IN document("bib.xml")/bib/book <when> 2004 </when> <when> 2004 </when> RETURN <answer> RETURN <answer> </answer> </answer> <title> $x/title/text() </title> <title> $x/title/text() </title> <answer> <answer> <year> $x/year/text() </year> <year> $x/year/text() </year> <what> Turkeys on TV</what> <what> Turkeys on TV</what> </answer> </answer> <when> 2002 </when> <when> 2002 </when> </answer> </answer> . . . . . . . . . . 9 10 XQuery: Nesting XQuery Result: For each author of a book by Morgan <result> <result> Kaufmann, list all books she published: <author>Jones</author> <author>Jones</author> <title> abc </title> FOR $b IN document(“bib.xml”)/bib, <title> abc </title> FOR $b IN document(“bib.xml”)/bib, $a IN $b/book[publisher /text()=“Morgan Kaufmann”]/author <title> def </title> $a IN $b/book[publisher /text()=“Morgan Kaufmann”]/author <title> def </title> RETURN <result> RETURN <result> </result> </result> { $a, { $a, <result> <result> FOR $t IN $b/book[author/text()=$a/text()]/title FOR $t IN $b/book[author/text()=$a/text()]/title <author> Smith </author> <author> Smith </author> RETURN $t RETURN $t <title> ghi </title> <title> ghi </title> } } </result> </result> </result> </result> In the RETURN clause comma concatenates XML fragments 11 12 2
Aggregates Aggregates Find all books with more than 3 authors: Same thing: FOR $x IN document("bib.xml")/bib/book FOR $x IN document("bib.xml")/bib/book[count(author)>3] FOR $x IN document("bib.xml")/bib/book FOR $x IN document("bib.xml")/bib/book[count(author)>3] WHERE count($x/author)>3 RETURN $x WHERE count($x/author)>3 RETURN $x RETURN $x RETURN $x count = a function that counts avg = computes the average sum = computes the sum distinct-values = eliminates duplicates 13 14 Aggregates Aggregates Print all authors who published more than 3 Be aware of duplicates ! books FOR $b IN document("bib.xml")/bib, FOR $b IN document("bib.xml")/bib, FOR $b IN document("bib.xml")/bib, FOR $b IN document("bib.xml")/bib, $a IN distinct-values($b/book/author/text()) $a IN distinct-values($b/book/author/text()) $a IN $b/book/author/text()) $a IN $b/book/author/text()) WHERE count($b/book[author/text()=$a)>3 WHERE count($b/book[author/text()=$a)>3 WHERE count($b/book[author/text()=$a)>3 WHERE count($b/book[author/text()=$a)>3 RETURN <author> { $a } </author> RETURN <author> { $a } </author> RETURN <author> { $a } </author> RETURN <author> { $a } </author> What’s wrong ? 15 16 XQuery FOR-WHERE-RETURN Find books whose price is larger than average: • “Flatten” the authors, i.e. return a list of (author, title) pairs FOR $b in document(“bib.xml”)/bib FOR $b in document(“bib.xml”)/bib FOR $b IN document("bib.xml")/bib/book, LET $a:=avg($b/book/price/text()) FOR $b IN document("bib.xml")/bib/book, Answer: LET $a:=avg($b/book/price/text()) $x IN $b/title/text(), <answer> $x IN $b/title/text(), FOR $x in $b/book FOR $x in $b/book <title> abc </title> $y IN $b/author/text() $y IN $b/author/text() WHERE $x/price/text() > $a WHERE $x/price/text() > $a <author> efg </author> RETURN <answer> RETURN <answer> </answer> RETURN $x RETURN $x <title> { $x } </title> <title> { $x } </title> <answer> <author> { $y } </author> <author> { $y } </author> <title> abc </title> </answer> <author> hkj </author> LET binds a variable to one value; </answer> </answer> FOR iterates a variable over a list of values 17 18 We will come back to that 3
Recommend
More recommend
