XQuery

XQuery is a functional language in which a query is represented as an expression. It is a language that uses the structure of XML intelligently can express queries across all these kinds of data, whether physically stored in XML or viewed as XML via middleware. It is designed to with a human-readable query syntax and an XML-based query syntax. It overlaps with features: from XPath [XPath] and XQL [XQL] it took a path expression syntax suitable for hierarchical documents. From XML-QL [XML-QL] it took the notion of binding variables and then using the bound variables to create new structures. From SQL [SQL] it took the idea of a series of clauses based on keywords that provide a pattern for restructuring data (the SELECT-FROM-WHERE pattern in SQL).

The principal forms of XQuery expressions are as follows:

  1. Path expressions
  2. Element constructors
  3. FLWR expressions
  4. Expressions involving operators and functions
  5. Conditional expressions
  6. Quantified expressions
  7. List constructors
  8. Expressions that test or modify datatypes

A path expression consists of a series of steps. Each step represents movement through a document in a particular direction, and each step can apply one or more predicates to eliminate nodes that fail to satisfy a given condition. The result of each step is a list of nodes that serves as a starting point for the next step.

A path expression can begin with an expression that identifies a specific node, such as the function document(string), which returns the root node of a named document. A query can also contain a path expression beginning with “/” or “//” which represents an implicit root node, determined by the environment in which the query is executed.

 

XPath abbreviated syntax

XQuery uses path expressions to navigate through elements in an XML document. XPath abbreviated syntax can be found in [XPath].

. Denotes the current node.
.. Denotes the parent of the current node.
/ Denotes the root node, or a separator between steps in a path.
// Denotes descendants of the current node.
@ Denotes attributes of the current node.
* Denotes “any” (node with unrestricted name).
[ ] Brackets enclose a Boolean expression that serves as a predicate for a given step.
[n] When a predicate consists of an integer, it serves to select the element with the given ordinal number from a list of elements.

The following table illustrates the use of quotes in XQuery, which is slightly different from that in XML:

Example Meaning
FOR XQuery keyword
‘FOR’ Identifier (e.g., tagname)
“FOR” Literal string value
\” Literal double-quote (used in a string)
\’ Literal single-quote (used in a string)

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

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

[price<30]

FLWR Expressions

  • 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)

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.

The following FLWOR expression will select exactly the same as the path expression above:

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

The at keyword can be used to count the iteration:

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

XQuery Basic Syntax Rules

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

“If-Then-Else” expressions are allowed in XQuery.

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>

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

 

XQuery Comparisons

In XQuery there are two ways of comparing values.

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

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

 

XQuery User-Defined Functions

If you cannot find the XQuery function you need, you can write your own.

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
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>

Querying Multiple Files in a Directory

The functionโ€™s declaration for this feature is:

<books>{ 
for $book in collection("file:///c:/books?select=*.xml") 
return 
 <myBook>{$book/book/title}</myBook> 
}</books>

The functionโ€™s declaration for this feature is:

collection(“directory_uri(?option(;option)*)?”)
where:

directory_uri is a URI referencing a directory. The URI must use the file:// scheme.

option is {(select=”REGEX”) | recurse={yes | no} | (sort=[a,t,r]+) | (xquery-regex=(yes|no))}

where:

  • select contains a regular expression (REGEX), which determines which files in the directory are selected. If select is not specified, any file is assumed.
  • sort determines how the retrieved files are sorted, as follows:
    a sorts alphabetically (ascending).
    t sorts by modification time (beginning with most recent).
    r combined with a and t reverses the sort order.
    recurse determines whether subdirectories are searched. The default is no. To search subdirectories, set this option to yes, for example:

    <books>{
    for $book in collection("file:///c:/books?select=*.xml;recurse=yes")
    return
    <myBook>{$book/book/title}</myBook>
    }</books>
  • xquery-regex determines what type of regular expression syntax is specified in select.

Example Code

xquery version "1.0";
declare namespace xsd = "http://www.w3.org/2001/XMLSchema";
declare namespace xsi = "http://www.w3.org/2001/XMLSchema-instance";
declare namespace r10Ex="http://www.Retalix.com/Extensions";

(: The XML document ๐Ÿ™‚
declare variable $documents := collection("file:/D:/Sainsburys/workspacexml/promotions?select=*.xml;recurse=yes");
(:declare variable $document as document-node() := doc("R10xmlsample1.xml");:)
declare variable $count-document as xs:decimal := count($documents//POSLog);

(: Count of all items ๐Ÿ™‚
declare variable $count-items as xs:decimal := count($documents//LineItem);
declare variable $sum-items-price as xs:double := sum($documents//LineItem/Sale/ActualSalesUnitPrice) cast as xs:double;
(:declare variable $sum-items-tax as xs:double := sum($document//LineItem/Sale/Tax/Amount) cast as xs:double;:)
declare variable $sum-items-tax as xs:double := sum($documents//LineItem/Sale/MerchandiseHierarchy[@ID="Tax"]) cast as xs:double;



(: Filter by price ๐Ÿ™‚
declare variable $price-filter as xs:decimal := 1.95;
declare variable $tax-filter as xs:decimal := 0;
(:declare variable $transactions-filtered := $document//LineItem/Sale[ActualSalesUnitPrice < $price-filter];:)
declare variable $transactions-filtered := $documents//LineItem/Sale[MerchandiseHierarchy[@ID="Tax"] > $tax-filter];
declare variable $transactions := $transactions-filtered;
(:declare variable $transactions := $document//RetailTransaction/LineItem/Sale;:)


(: Sum of filtered items ๐Ÿ™‚
declare variable $sum-items-price-filtered as xs:double := sum($transactions/ActualSalesUnitPrice) cast as xs:double;
(:declare variable $sum-items-tax-filtered as xs:double := sum($transactions/Tax/Amount) cast as xs:double;:)
declare variable $sum-items-tax-filtered as xs:double := sum($transactions/MerchandiseHierarchy[@ID="Tax"]) cast as xs:double;


(: function -> List of all lineitems ๐Ÿ™‚
declare function r10Ex:list-lineitems() {

        for $document in $documents
    
            let $transactionID := $document//Transaction/TransactionID/text()
            let $businessUnit := $document//BusinessUnit/UnitID/string(@Name)
            let $operator := $document//Transaction/OperatorID/string(@OperatorName)
            let $lineitems := $document//LineItem/Sale
            for $lineitem at $index in $lineitems
               (:where $lineitem/RegularSalesUnitPrice = $price:)
            (:order by $lineitem/ActualSalesUnitPrice descending:)
            return
                  <lineItem>
                     {$index}, &#32; { $transactionID}, &#32; {$businessUnit}, &#32; {$operator}, &#32; {$lineitem/Description/text()/normalize-space()}, &#32; {$lineitem/RegularSalesUnitPrice/text()} &#32; {$lineitem/RegularSalesUnitPrice/string(@Currency)}, &#32; {$lineitem/MerchandiseHierarchy[@ID="Tax"]/text()} &#32;{$lineitem/Tax/Amount/string(@Currency)}
                  </lineItem> 
};




(: Output ๐Ÿ™‚
<transactions>
<count>
    <document>{$count-document}</document>
    <total>{$count-items}</total>
    <filtered>{count($transactions)}</filtered>
</count>
<sum>
    <price>
        <total>{$sum-items-price}</total>
        <filtered>{$sum-items-price-filtered}</filtered>
    </price>
    <tax>
        <total>{$sum-items-tax}</total>
        <filtered>{$sum-items-tax-filtered}</filtered>
    </tax>
</sum>
<lineitems>
    <lineItem>No, TransactionID, UnitID, Operator, Description, RegularSalesUnitPrice, Tax</lineItem>
    {r10Ex:list-lineitems()}
</lineitems>
</transactions>

 

 

References