Megan Taylor

front-end dev, volunteacher, news & data junkie, bibliophile, Flyers fan, sci-fi geek and kitteh servant

Intro to Databases: Querying XML, XQuery Demo

Use Bookstore data

Titles of books costing less than $90 where “Ullman” is an author
for $b in doc(“BookstoreQ.xml”) /Bookstore/Book
where $b/@Price < 90 and $b/Authors/Author/Last_Name = "Ullman" return $b/Title Titles and author first names of books whose titles contains one of the author's first names for $b in doc("BookstoreQ.xml") /Bookstore/Book where some $fn in $b/Authors/Author/First_Name satisfies contains($b/Title, $fn) return
{ $b/Title }
{ $b/Authors/Author/First_Name }

Titles and author first names of books whose titles contains one of the author’s first names – but only include first names in the book title
for $b in doc(“BookstoreQ.xml”) /Bookstore/Book
where some $fn in $b/Authors/Author/First_Name
satisfies contains($b/Title, $fn)
return
{ $b/Title }
{ for $fn in $b/Authors/Author/First_Name where contains($b/Title, $fn) return $fn }

Find the average book price

{ let $plist := doc(“BookstoreQ.xml”) /Bookstore/Book/@Price
return avg($plist) }

output is XML
also

{ let $a := avg(doc(“BookstoreQ.xml”) /Bookstore/Book/@Price)
return $a }

Books whose price is below average
let $a := avg(doc(“BookstoreQ.xml”) /Bookstore/Book/@Price)
for $b in doc(“BookstoreQ.xml”) /Bookstore/Book
where $b/@Price < $a return
{ $b/Title }
{ $b/data(@Price) }

Titles and prices sorted by price
for $b in doc(“BookstoreQ.xml”) /Bookstore/Book
order by xs:int($b/@Price)
return
{ $b/Title }
{$b/data(@Price) }

All last names
for $n in doc(“BookstoreQ.xml”) //Last_Name
return $n

All last names, no duplicates
for $n in distinct-values(doc(“BookstoreQ.xml”) //Last_Name)
return { $n }

Books where every author’s first name includes “J”
for $b in doc(“BookstoreQ.xml”) /Bookstore/Book
where every $fn in $b/Authors/Author/First_Name
satisfies contains($fn, “J”)
return $b

All pairs of titles containing a shared author
for $b1 in doc(“BookstoreQ.xml”) /Bookstore/Book
for $b2 in doc(“BookstoreQ.xml”) /Bookstore/Book
where $b1/Authors/Author/Last_Name = $b2/Authors/Author/Last_Name
and $b1/Title < $b2/Title return
{ data($b1/Title) }
{ data($b2/Title) }

Invert data: Authors with the books they’ve written, assuming author last names are unique

{ for $ln in distinct-values(doc(“BookstoreQ.xml”) //Author/Last_Name)
for $fn in distinct-values(doc(“BookstoreQ.xml”) //Author[Last_Name = $ln]/First_Name)
return

{$fn }
{$ln }
{ for $b in doc(“BookstoreQ.xml”) /Bookstore/Book[Authors/Author/Last_Name = $ln] return
{ $b/@ISBN } {$b/@Price } { $b/@Edition } { $b/Title } { $b/Remark }
}
}

November 16, 2011 | Comments Off on Intro to Databases: Querying XML, XQuery Demo | Categories: Posts | Permalink

Comments are closed.

%d bloggers like this: