Download
xquery n.
Skip this Video
Loading SlideShow in 5 Seconds..
XQuery PowerPoint Presentation

XQuery

161 Vues Download Presentation
Télécharger la présentation

XQuery

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. XQuery Slides From Dr. Suciu

  2. FLWR (“Flower”) Expressions FOR ... LET... WHERE... RETURN...

  3. FOR-WHERE-RETURN Find all book titles published after 1995: Result: <title> abc </title> <title> def </title> <title> ghi </title> for $x in doc("bib.xml")/bib/book where data($x/@year) > 1999 return $x/title doc(“bib.xml”) in freexml editor by default looks for bib.xml in the default directory where it was installed

  4. Equivalently (perhaps more geekish) FOR-WHERE-RETURN FOR$xINdoc("bib.xml")/bib/book[@year > 1995] /title RETURN$x And even shorter: doc("bib.xml")/bib/book[@year > 1995] /title

  5. FOR-WHERE-RETURN • Find all book titles and the year when they were published: for $x in doc("bib.xml")/bib/book return <answer> <what> {data($x/title)}</what> <when> {data($x/@year)}</when> </answer> We can construct whatever XML results we want !

  6. Answer <answer> <what>TCP/IP Illustrated</what> <when>1994</when> </answer> <answer> <what>Advanced Programming in the UNIX Environment</what> <when>1992</when> </answer> <answer> <what>Data on the Web</what> <when>2000</when> </answer> <answer> <what>The Economics of Technology and Content for Digital TV</what> <when>1999</when> </answer>

  7. FOR-WHERE-RETURN • Notice the use of “{“ and “}” • What is the result without them ? FOR$xINdoc("bib.xml")/bib/bookRETURN <answer> <title> $x/title/text() </title> <year> $x/year/text() </year> </answer>

  8. Aggregates Find all books with more than 3 authors: FOR$x IN doc("bib.xml")/bib/bookWHEREcount($x/author)>3 RETURN$x for $l in distinct-values(doc("bib.xml")//author/last) return <last>{ $l }</last> count = a function that counts avg = computes the averagesum = computes the sumdistinct-values = eliminates duplicates

  9. Aggregates <answer> { for $b in doc(“bib.xml")//book let $c := $b/author return <book>{ $b/title, <count>{ count($c) }</count>}</book> }</answer>

  10. XQuery Find books whose price is larger than average: <answer> { for $b in doc("bib.xml")/bib let $a := avg($b/book/price/text()) for $x in $b/book where ($x/price/text() > $a) return $x }</answer> LET binds a variable to one value; FOR iterates a variable over a list of values

  11. FOR v.s. LET Returns: <result> <book>...</book></result> <result> <book>...</book></result> <result> <book>...</book></result> ... FOR$xIN /bib/book RETURN <result> { $x } </result> LET$x := /bib/book RETURN <result> { $x } </result> Returns: <result> <book>...</book> <book>...</book> <book>...</book> ... </result>

  12. For vs Let (cont’d) for $i in (1, 2, 3), $j in (4, 5, 6) return <tuple> <i>{ $i }</i> <j>{ $j }</j> </tuple> for $i in (1, 2, 3) let $j := "a" return <tuple><i>{ $i }</i><j>{ $j }</j></tuple> for $i in (1, 2, 3) let $j := ( 4 to 6) return <tuple> <i> { $i }</i> <j>{ $j }</j> </tuple>

  13. XQuery: Nesting For each author of a book by Morgan Kaufmann, list all books she published: FOR$b IN doc(“bib.xml”)/bib,$aIN$b/book[publisher /text()=“Morgan Kaufmann”]/author RETURN <result> { $a, FOR$tIN$b/book[author/text()=$a/text()]/title RETURN$t} </result>

  14. XQuery Result: <result> <author>Jones</author> <title> abc </title> <title> def </title> </result> <result> <author> Smith </author> <title> ghi </title> </result>

  15. Inverting Hierarchy List of titles published by each publisher • <listings> • { • for $p in distinct-values(doc("bib.xml")//publisher) • order by $p • return • <result> • { $p } • { • for $b in doc("bib.xml")/bib/book • where $b/publisher = $p • order by $b/title • return $b/title • } • </result> • } • </listings>

  16. FOR$x in doc(“db.xml”)/db/Product/rowORDER BY$x/price/text()RETURN <answer> { $x/name, $x/price } </answer> SELECT x.name, x.priceFROM Product xORDER BY x.price SQL XQuery SQL and XQuery Side-by-side Find all product names, prices,sort by price Product(pid, name, maker, price)

  17. Answers <answer> <name> abc </name> <price> 7 </price></answer> <answer> <name> def </name> <price> 23 </price></answer> . . . . Notice: this is NOT awell-formed document !(WHY ???)

  18. Producing a Well-Formed Answer <myQuery> { FOR$x in doc(“db.xml”)/db/Product/rowORDER BY$x/price/text()RETURN <answer> { $x/name, $x/price } </answer> }</myQuery>

  19. Xquery’s Answer <myQuery> <answer> <name> abc </name> <price> 7 </price> </answer> <answer> <name> def </name> <price> 23 </price> </answer> . . . .</myQuery> Now it is well-formed !

  20. FOR$r in doc(“db.xml”)/db,$x in $r/Product/row,$y in $r/Company/rowWHERE$x/maker/text()=$y/cid/text() and $y/city/text() = “Seattle”RETURN { $x/name } SELECT x.nameFROM Product x, Company yWHERE x.maker=y.cid and y.city=“Seattle” SQL XQuery FOR$y in /db/Company/row[city/text()=“Seattle”],$x in /db/Product/row[maker/text()=$y/cid/text()]RETURN { $x/name } CoolXQuery SQL and XQuery Side-by-side Product(pid, name, maker, price)Company(cid, name, city, revenues) Find all products made in Seattle

  21. <product> <row> <pid> 123 </pid> <name> abc </name> <maker> efg </maker> </row> <row> …. </row> … </product><product> . . . </product>. . . .

  22. SQL and XQuery Side-by-side For each company with revenues < 1M count the products over $100 SELECT y.name, count(*)FROM Product x, Company yWHERE x.price > 100 and x.maker=y.cid and y.revenue < 1000000GROUP BY y.cid, y.name FOR$r in doc(“db.xml”)/db,$y in $r/Company/row[revenue/text()<1000000]RETURN <proudCompany> <companyName> { $y/name/text() } </companyName> <numberOfExpensiveProducts> { count($r/Product/row[maker/text()=$y/cid/text()][price/text()>100]) } </numberOfExpensiveProducts> </proudCompany>

  23. SQL and XQuery Side-by-side Find companies with at least 30 products, and their average price SELECT y.name, avg(x.price)FROM Product x, Company yWHERE x.maker=y.cidGROUP BY y.cid, y.nameHAVING count(*) > 30 An element FOR$r in doc(“db.xml”)/db,$y in $r/Company/rowLET$p := $r/Product/row[maker/text()=$y/cid/text()]WHEREcount($p) > 30RETURN <theCompany> <companyName> { $y/name/text() } </companyName> <avgPrice> avg($p/price/text()) </avgPrice> </theCompany> A collection