XQuery

 

What is XQuery?

XQuery is to XML what SQL is to databases.
XQuery was designed to query XML data.

F= for  (from)
L=let
W=where
O=order
R=result (select)

What is FLWOR?

FLWOR (pronounced "flowr") is an acronym for "For, Let, Where, Order by, Return".

• For - selects a sequence of nodes
• Let - binds a sequence to a variable
• Where - filters the nodes
• Order by - sorts the nodes
• Return - what to return (gets evaluated once for every node)

Ex:

How to Select Nodes From "books.xml"?

<bookstore>
<book category="cooking">
<title lang="en">Everyday Italian</title>
<author>Giada De Laurentiis</author>
<year>2005</year>
<price>30.00</price>
</book>
<book category="children">
<title lang="en">Harry Potter</title>
<author>J K. Rowling</author>
<year>2005</year>
<price>29.99</price>
</book>
<book category="web">
<title lang="en">XQuery Kick Start</title>
<author>James McGovern</author>
<author>Per Bothner</author>
<author>Kurt Cagle</author>
<author>James Linn</author>
<author>Vaidyanathan Nagarajan</author>
<year>2003</year>
<price>49.99</price>
</book>
<book category="web" cover="paperback">
<title lang="en">Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>
</bookstore>

Functions: 

XQuery uses functions to extract data from XML documents.
The doc() function is used to open the "books.xml" file:
doc("books.xml")

Path Expressions

XQuery uses path expressions to navigate through elements in an XML document.

The following path expression is used to select all the title elements in the "books.xml" file:

doc("books.xml")/bookstore/book/title

(/bookstore selects the bookstore element, /book selects all the book elements under the bookstore element, and /title selects all the title elements under each book element) 

<title lang="en">Everyday Italian</title>
<title lang="en">Harry Potter</title>
<title lang="en">XQuery Kick Start</title>
<title lang="en">Learning XML</title>

Predicates

XQuery uses predicates to limit the extracted data from XML documents.

doc("books.xml")/bookstore/book[price<30]

<book category="CHILDREN">
  <title lang="en">Harry Potter</title>
  <author>J K. Rowling</author>
  <year>2005</year>
  <price>29.99</price>
</book>

doc("books.xml")/bookstore/book[price>30]/title

The expression above will select all the title elements under the book elements that are under the bookstore element that have a price element with a value that is higher than 30.

<title lang="en">XQuery Kick Start</title>
<title lang="en">Learning XML</title>

FLWOR expression 

for $x in doc("books.xml")/bookstore/book
where $x/price>30
order by $x/title
return $x/title 

The for clause selects all book elements under the bookstore element into a variable called $x.

The where clause selects only book elements with a price element with a value greater than 30.

The order by clause defines the sort-order. Will be sort by the title element.

The return clause specifies what should be returned. Here it returns the title elements.

The result of the XQuery expression above will be:

<title lang="en">Learning XML</title>
<title lang="en">XQuery Kick Start</title>

Nodes

In XQuery, there are seven kinds of nodes: element, attribute, text, namespace, processing-instruction, comment, and document (root) nodes.

Look at the following XML document:

<?xml version="1.0" encoding="UTF-8"?>

<bookstore>  (root node)
<book>
  <title lang="en">Harry Potter</title>  (attribute node)
  <author>J K. Rowling</author>  (element node)
  <year>2005</year>
  <price>29.99</price>
</book>
</bookstore>

XQuery Syntax

Some basic syntax rules:

• XQuery is case-sensitive
• XQuery elements, attributes, and variables must be valid XML names
• An XQuery string value can be in single or double quotes
• An XQuery variable is defined with a $ followed by a name, e.g. $bookstore
• XQuery comments are delimited by (: and :), e.g. (: XQuery Comment :)

XQuery Conditional Expressions 

1)  If-Then-Else" expressions

Notes on the "if-then-else" syntax: parentheses around the if expression are required. else is required, but it can be just else ().

for $x in doc("books.xml")/bookstore/book
return if ($x/@category="CHILDREN")
then <child>{data($x/title)}</child>
else <adult>{data($x/title)}</adult>

The result of the example above will be:

<adult>Everyday Italian</adult>
<child>Harry Potter</child>
<adult>XQuery Kick Start</adult>
<adult>Learning XML</adult>

 

XQuery Comparisons

In XQuery there are two ways of comparing values.

1. General comparisons: =, !=, <, <=, >, >=

2. Value comparisons: eq, ne, lt, le, gt, ge

The difference between the two comparison methods are shown below.

The following expression returns true if any q attributes have a value greater than 10:

$bookstore//book/@q > 10

The following expression returns true if there is only one q attribute returned by the expression, and its value is greater than 10. If more than one q is returned, an error occurs:

$bookstore//book/@q gt 10

 

XQuery Selecting and Filtering

The for Clause

The for clause binds a variable to each item returned by the in expression. The for clause results in iteration. There can be multiple for clauses in the same FLWOR expression.

1)in

To loop a specific number of times in a for clause, you may use the in keyword:

for $x in (1 to 5)
return <test>{$x}</test>

Result:

<test>1</test>
<test>2</test>
<test>3</test>
<test>4</test>
<test>5</test>

2)at

The at keyword can be used to count the iteration:

for $x at $i in doc("books.xml")/bookstore/book/title
return <book> {$i}. {data($x)} </book>

Result:

<book>1. Everyday Italian</book>
<book>2. Harry Potter</book>
<book>3. XQuery Kick Start</book>
<book>4. Learning XML</book>

3)

It is also allowed with more than one in expression in the for clause.

Use comma to separate each in expression:

for $x in (10,20), $y in (100,200)
return <test> x={$x} and y={$y} </test>

Result:

<test>x=10 and y=100</test>
<test>x=10 and y=200</test>
<test>x=20 and y=100</test>
<test>x=20 and y=200</test>

4)

The let Clause:

The let clause allows variable assignments and it avoids repeating the same expression many times. The let clause does not result in iteration. 

let $x := (1 to 5)
return <test>{$x}</test> 

Result:

<test>1 2 3 4 5</test>

5)

The where Clause

The where clause is used to specify one or more criteria for the result:

where $x/price>30 and $x/price<100

6)

The order by Clause

The order by clause is used to specify the sort order of the result. Here we want to order the result by category and title:

for $x in doc("books.xml")/bookstore/book
order by $x/@category, $x/title
return $x/title

Result:

<title lang="en">Harry Potter</title>
<title lang="en">Everyday Italian</title>
<title lang="en">Learning XML</title>
<title lang="en">XQuery Kick Start</title> 

7)

The return Clause

The return clause specifies what is to be returned.

for $x in doc("books.xml")/bookstore/book
return $x/title

Result:

<title lang="en">Everyday Italian</title>
<title lang="en">Harry Potter</title>
<title lang="en">XQuery Kick Start</title>
<title lang="en">Learning XML</title>

XQuery Functions

You can also define your own functions in XQuery.

User-defined functions can be defined in the query or in a separate library.

Syntax

declare function prefix:function_name($parameter as datatype)
as returnDatatype
{
 ...function code here...
};

Notes on user-defined functions:

• Use the declare function keyword
• The name of the function must be prefixed
• The data type of the parameters are mostly the same as the data types defined in XML Schema
• The body of the function must be surrounded by curly braces

Example of a User-defined Function Declared in the Query

1)  declare function local:minPrice($p as xs:decimal?,$d as xs:decimal?)as xs:decimal?

{

    let $disc := ($p * $d) div 100

    return ($p - $disc)

};

Below is an example of how to call the function above:

<minPrice>{local:minPrice($book/price,$book/discount)}</minPrice>

2) Reference XML:

<?xml Version=1.0?>

      <fruitdetails>

          <fruit>

                <name available=”No”>pineapple</name>

               <quantity>6</ quantity >

                <price>50.0</ price >

          </fruit>

 </fruitdetails > 

XQuery functions:

Xquery version “1.0”;
 declare function local:totalpricewith tax($quality as element(fruitdetails),$pice as element(fruitdetails)) as xs:decimal?

{

    Let $totalprice := ($price/fruit/price  *$quantity/fruit/quantity)

    Return($totalprice + 10.00)

};

Call Functions:

<TotalPrice>{ local:totalpricewithtax(doc(“FruitOrderSingle.xml”)/fruitdetails,doc(FruitOrderSingle.xml)/ fruitdetails)}

</Totalprice>

Output:

<TotalPrice>301</Totalprice>

3) Reference XML:

<?xml Version=1.0?>
      <fruitdetails>
          <fruit>
                <name available=”No”>Pineapple</name>
               <quantity>6</ quantity >
<price>49.99</ price >
          </fruit>
          <fruit>
                <name available=”Yes”>Apple</name>
               <quantity>24</ quantity >
<price>30.99</ price >
          </fruit>
          <fruit>
                <name available=”No”>Banana</name>
               <quantity>12</ quantity >
<price>29.99</ price >
          </fruit>
     </fruitdetails >
 

Xquery version “1.0”;
 declare function local:fruitorder()
{
                Let $newline := “&#10,”
                Let $tab :=  “$#9;”
For $x in doc(“FruitsOrder.xml”)/fruitsdetails/fruit
                Return concat(data($x/name),$tab,data($x/quantity),$tab,data($x/price) ,
                $tab,$x/name/@available,$newline
};

callFunction:

Local:fruitorder()     

Output:

Pineapple    6     49.99     No
Apple    24           30.99     yes
Banana     12    29.99         No 

1) (This query going to return Title of Books  costing less than  90 dollar where “ULLman” is an             author.)

For $b in doc(“Bookstore().xml”)/Bookstore/Book
Where $b/@price < 90 and
$b/Authors/Author/Last_Name=”Ullman”
Return $b/Title

o For $b in doc(“Bookstore().xml”)/Bookstore/Book

è For construct has an xpath expression which returns the book in a document,  

   it bind the variable with $b, each book one at a time and run rest of the quarry  for that binding.

o Where $b/@price < 90 and
$b/Authors/Author/Last_Name=”Ullman”

è Next it will check whether price of book is less than 90 dollar and last name under book element value is “ULLman ”

o    Return $b/Title

è If both condition is true then it will return title of the book. 

2) (Titles and Author first names of books whose title contains one of the author first names.) 

For $b in doc (“Bookstore.xml”)/Bookstore/Book
Where some $fn in $b/Author/First_Name
Satisfies contains($b/Title,  $fn)
Return<Book>
                {$b/Title}
                {$b/Authors/Author/First_Name}
</Book>

-> For clause bind $b to each book in database , in where condition we look for is a First_Name sub element of the book which get bound to $fn such that there are title of books contains a First_Name 

-> Some,in, satisfy are keyword. 

Output: 

<?xml version=”1.0” encoding=”UTF-8”?>
<BOOK>
                <Title>Hector and Jeff’s database Hints</title>
                <FirstName>Jeffrey</ FirstName >
                < FirstName >Hector</ FirstName>
</BOOK>
<BOOK>
                <Title>jenifer’s Economical Database Hints</title>
                <FirstName> jenifer</ FirstName >
</BOOK>

3) No include only first names in book title: restricting firstname which appear into title. 

For $b in doc (“Bookstore.xml”)/Bookstore/Book
Where some $fn in $b/Author/First_Name
Satisfies contains($b/Title, $fn)
Return<Book>
{$b/Title}
{for $fn in $b/Author/First_Name
Where contains($b/Title, $fn) return$fn }
</Book>

Output: 

<?xml version=”1.0” encoding=”UTF-8”?>
<BOOK>
<Title>Hector and Jeff’s database Hints</title>
< FirstName >Hector</ FirstName>
</BOOK>
<BOOK>
<Title>jenifer’s Economical Database Hints</title>
<FirstName> jenifer</ FirstName >
</BOOK>

4) This query finds the average book price

<Average>
{ let $plist := doc(“BookStore().xml”)/Bookstore/Book/@price
Return avg($plist) }
</ Average >

è We are going to assign our variable $plist to the result of this  expression ,(all the price attribute in the database assign to the plist  and return average of element in that list)

NOTE: for clause is iterator and let clause is assignment

Output: 

<?xml version=”1.0” encoding=”UTF-8”?>
< Average >65</ Average >

Or

<Average>
{ let $a := avg(doc(“BookStore().xml”)/Bookstore/Book/@price)
Return $a }
</ Average >

Output: 

<?xml version=”1.0” encoding=”UTF-8”?>
                < Average >65</ Average >

5) Let and for expression:

Find all book whose price is below average.

let $a :=  avg(doc(“BookStore().xml”)/Bookstore/Book/@price
for $b in doc (“Bookstore.xml”)/Bookstore/Book
where $b/@price < $a
return  <Book>
{   $b/title   }
<Price>{    $b/data(@price)   }</Price>
</Book>

Output:

<?xml version=”1.0” encoding=”UTF-8”?>
   <Book>
            <Title>Hector and Jeff’s database Hints</title>
           <Price> 50</Price>
 </Book>
<Book>
                <Title>jenifer’s Economical Database Hints</title>
                <Price> 25</Price>
</Book>

6) OrderBy

for $b in doc (“Bookstore.xml”)/Bookstore/Book
order by  $b/@price
return  <Book>
{   $b/title   }
<Price>{    $b/data(@price)   }</Price>
</Book>

Output:

<?xml version=”1.0” encoding=”UTF-8”?>
   <Book>
            <Title>databaseSystems: The Complete book</title>
           <Price>100</Price>
 </Book>
<Book>
                <Title>jenifer’s Economical Database Hints</title>
                <Price> 25</Price>
</Book>
 <Book>
            <Title>Hector and Jeff’s database Hints</title>
           <Price> 50</Price>
 </Book>
<Book>
                <Title>First cource in database system</title>
                <Price> 85</Price>
</Book>

Note: Price is string so call built in function

xs:int  which convert the value in integer. So:

for $b in doc (“Bookstore.xml”)/Bookstore/Book
order by  xsl:int ($b/@price )
return  <Book>
{   $b/title   }
<Price>{    $b/data(@price)   }</Price>
</Book> 

Output:

<?xml version=”1.0” encoding=”UTF-8”?>
<Book>
                <Title>jenifer’s Economical Database Hints</title>
                <Price> 25</Price>
</Book>
 <Book>
            <Title>Hector and Jeff’s database Hints</title>
           <Price> 50</Price>
 </Book>
<Book>
                <Title>First cource in database system</title>
                <Price> 85</Price>
</Book>
   <Book>
            <Title>databaseSystems: The Complete book</title>
           <Price>100</Price>
 </Book>

7)  Duplication Elimination

for $n in doc (“Bookstore.xml”)//Last_Name    [// looks in depth of xml tree]

return $n 

Output:

<?xml version=”1.0” encoding=”UTF-8”?>

<last_Name>Ullman</Last_name>

<last_Name>Widom</Last_name>

<last_Name>Garcia-Molina</Last_name>

<last_Name>Ullman</Last_name>

<last_Name>Widom</Last_name>

<last_Name>Ullman</Last_name>

<last_Name>Garcia-Molina</Last_name>

<last_Name>Widom</Last_name>  

for $n in distinct-value(doc (“Bookstore.xml”)//Last_Name   )

    return <Last_Name>  {$n} </Last_Name>

Output:

<?xml version=”1.0” encoding=”UTF-8”?>

<last_Name>Ullman</Last_name>

<last_Name>Widom</Last_name>

<last_Name>Garcia-Molina</Last_name>

8) Every author’s  first name includes “J”

for $b in doc (“Bookstore.xml”)/Bookstore/Book

where every $fn in $b/Authors/Author/First_Name

                satisfies contains($fn ,   “j”)   [satisfy the condition first name contain letter j]

return $b 

Output:

<?xml version=”1.0” encoding=”UTF-8”?>
<Book ISBN=”ISBN-0-13” Price=”85” Edition=”3rd”>
<Title>A First Cource in Database System</Title>
     <Authors>    
                <Author>
                                <First_Name>Jeffrey</First_Name>
                                <Last_Name>Ullman</Last_Name>
                </Author>
                <Author>
                                <First_Name>Jennifer</First_Name>
                                <Last_Name>widom</Last_Name>
                </Author>
       </Authors>
</Book>

9)self-join

Call title of books which share common author and their last name.

for $b1 in doc (“Bookstore.xml”)/Bookstore/Book

for $b2 in doc (“Bookstore.xml”)/Bookstore/Book

 where $b1/Authors/Author/Last_Name = $b2/Authors/Author/Last_Name

     and

 $b1/Title < $b2/Title

Return

  <BookPair>

      <Title1> { data($b1/Title)   }</ Title1>

      < Title2>{ data($b2/Title)   }</ Title2>

 </BookPair> 

10) if-else:

For $x in doc(“Fruits.xml”)/fruitDetails/fruit

Return if($x/name/@available=”Yes”)

                Then<li>{data($x/name)}.Available In Store</li>

                else<li>{data($x/name)}.Not Available In Store</li>