1 / 23

XQuery

XQuery. Slides From Dr. Suciu. FLWR (“Flower”) Expressions. FOR ... LET ... WHERE ... RETURN. FOR-WHERE-RETURN. Find all book titles published after 1995:. Result: < title > abc </ title > < title > def </ title > < title > ghi </ title >.

yuri-dean
Download Presentation

XQuery

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related