Learn XQuery

Restructuring Data: FLWOR Expressions

XQuery has an expression called a FLWOR expression, which is similar to a SQL Select statement that that has From and Where clauses. FLWOR is pronounced "flower", and is an acronym for the keywords used to introduce each clause (for, let, where, order by, and return).

Here is a FLWOR expression that returns holdings for AMZN.

for $h in collection('holdings')/holdings 
where $h/stockticker = 'AMZN' 
order by $h/shares 
return $h

In the preceding query, the FLWOR expression performs the following functions:

The for clause binds the variable $h to each holdings element.

The where clause filters out bindings of $h for which the stockticker element does not contain the value AMZN.

The order by clause determines the order.

The return clause produces a result for each binding of $h.

FLWOR expressions are frequently used to combine related information. The possible combinations are generated by using variables in the for clause and using a where clause to filter out combinations that are not useful. This is known as a "join". Consider the following expression:

for $u in collection('users')/users, 
    $h in collection('holdings')/holdings 
where $u/userid=$h/userid     
order by $u/lastname, $u/lastname 
return 
  <holding> 
     { 
       $u/firstname, 
       $u/lastname, 
       $h/stockticker, 
       $h/shares 
     } 
  </holding>

This expression finds every pair of users elements and holdings elements whose userid child element has the same value, and then builds a holding element that describes the user and his holdings.

Now, let's look at a FLWOR expression that uses a let clause:

let $h :=  collection('holdings')/holdings 
return count($h)

A let clause binds a variable to a sequence, which often contains more than one item. In the preceding query, $h is bound to all of the holdings elements in the collection, and the return clause is evaluated. Note the difference between a for clause and a let clause: A for clause always iterates over a sequence, binding a variable to each item; a let clause simply binds a variable to the entire sequence.

In the preceding expression, the result is 8. In contrast, if you use the following for clause:

for $h in collection('holdings')/holdings 
return count($h)

The result is a sequence of numbers: 1 1 1 1 1 1 1 1.

In some cases, you may find it useful to combine for and let clauses. In the following expression, these two clauses are combined to produce a result that counts the number of stock holdings for each user.

for $u in collection('users')/users 
let $h := collection('holdings')/holdings[userid=$u/userid]     
order by $u/lastname, $u/firstname 
 
return 
    <user nstocks="{count($h)}"> 
       { 
         $u/firstname, 
         $u/lastname 
       } 
    </user>

XML Reporting for Relational Sources

Many applications need to create rich XML structures from relational sources. For example, Web sites generally create hierarchical displays of the data found in a relational database, and web messages are often very complex hierarchical structures. For these applications, XQuery can act as an "XML report writer".

The database tables used in this section are as follows:

users Table

userid   firstname   lastname   othername   
 
Minollo  Carlo       Innocenti              
Maggie   Maggie      Pristera      William

holdings Table

userid   stockticker   shares              
 
Maggie PRGS          23 
Minollo  PRGS          4000000 
. 
. 
.

statistical Table

id companyname            ticker percentagechange annualrevenues location 
 
1  Apple Computer, Inc.   AAPL   -40.80%          5250           Cupertino 
2  Accrue Software, Inc.  ACRU   -57.60%          4.21           Freemont 
. 
. 
.

Here is a query that creates a portfolio for each user.

<portfolios> 
 { 
    for $u in collection('users')/users 
    order by $u/userid 
    return 
      <portfolio id="{$u/userid}"> 
        <name> 
          <first>{data($u/firstname)}</first> 
          <last>{data($u/lastname)}</last> 
        </name> 
        <stocks> 
          { 
            for $h in collection('holdings')/holdings 
            where $h/userid = $u/userid 
            order by $h/stockticker 
            return 
              <stock> 
                <ticker>{data($h/stockticker)}</ticker> 
                <shares>{data($h/shares)}</shares> 
              </stock> 
          } 
        </stocks> 
      </portfolio> 
 } 
</portfolios>

Here is the result of the preceding query.

<portfolios> 
  <portfolio id="Maggie"> 
    <name> 
      <first>Maggie</first> 
      <last>Pristera</last> 
    </name> 
    <stocks> 
      <stock> 
        <ticker>AMZN</ticker> 
        <shares>3000</shares> 
      </stock> 
      <stock> 
        <ticker>EBAY</ticker> 
        <shares>4000</shares> 
      </stock> 
      <stock> 
        <ticker>IBM</ticker> 
        <shares>2500</shares> 
      </stock> 
      <stock> 
        <ticker>PRGS</ticker> 
        <shares>23</shares> 
      </stock> 
    </stocks> 
  </portfolio> 
  <portfolio id="Minollo"> 
    <name> 
      <first>Carlo</first> 
      <last>Innocenti</last> 
    </name> 
    <stocks> 
      <stock> 
        <ticker>AMZN</ticker> 
        <shares>3000</shares> 
    </stock> 
    <stock> 
      <ticker>EBAY</ticker> 
      <shares>4000</shares> 
    </stock> 
    <stock> 
      <ticker>LU</ticker> 
      <shares>40000</shares> 
    </stock> 
     <stock> 
       <ticker>PRGS</ticker> 
       <shares>4000000</shares> 
     </stock> 
   </stocks> 
  </portfolio> 
</portfolios>

NOTE: In the preceding query the data function is used, which returns only the value of the stockticker column. Without the data function, the value would be surrounded with an element named stockticker, resulting in, for example:

<ticker> 
  <stockticker>AMZN</stockticker> 
</ticker>

Processing XML and Relational Together

In some applications, you may need to use XML and relational data together. For example, a configuration file or an incoming web message might provide information needed to parameterize a query. Suppose you have an XML file that contains a request for a particular kind of report, and your query is to produce that report. For example, the following XML file, request.xml, contains a request to show the performance of Maggie's stocks during the period from 2003-01-01 to 2004-06-01.

<?xml version="1.0"?> 
<request> 
  <performance> 
    <UserId>Maggie</UserId> 
    <start>2003-01-01</start> 
    <end>2004-06-01</end> 
  </performance> 
</request>

Here is a query that creates a portfolio for the user specified in a request file, during the requested period.

declare base-uri "file:///c:/programs/examples/JoinXMLToRelational/"; 
 
declare variable $request := doc('request.xml')/request; 
 
for $user in $request/performance/UserId, 
    $start in $request/performance/start, 
    $end in $request/performance/end 
return 
 <portfolio UserId="{$user}"> 
  { $request } 
  { 
    for $st in collection('holdings')/holdings, 
        $stats in collection('statistical')/statistical 
    where $st/userid = $user 
      and $stats/ticker = $st/stockticker 
    return 
      <stock> 
        { $stats/companyname } 
        { $st/stockticker } 
        { $st/shares } 
        { $stats/annualrevenues } 
        { 
          let $hist :=  
            for $h in collection('historical')/historical 
            where $h/ticker = $st/stockticker 
              and xs:date($h/datetraded) gt xs:date($start) 
              and xs:date($h/datetraded) lt xs:date($end) 
            return $h 
          return 
            <performance> 
              <min>{min($hist/adjustedclose)}</min> 
              <max>{max($hist/adjustedclose)}</max> 
              <daily> 
               {  
                 for $h in $hist 
                 return <day>{$h/datetraded, $h/adjustedclose }</day> 
               } 
              </daily> 
            </performance> 
        } 
      </stock>  
  } 
 </portfolio>

Prev: "XML Constructors"

Try DataDirect XQuery® Free!

Put the power, scalability, and performance of DataDirect XQuery® to work for you today! Our free trial lets you see for yourself how easy it is to build data integration applications that access relational, EDI, and other file formats as XML!

Download DataDirect XQuery® today!

Online Video Tutorials!

Our easy-to-follow online video tutorials are a great way to get acquainted with the many features of DataDirect XQuery®.

And if you like what you see, download a free copy today and try DataDirect XQuery® for yourself!

Back to School with DataDirect XQuery®

Getting your mind around the possibilities of a data integration technology as promising as XQuery can be difficult, but our XML developers Webinars will help you understand the power and versatility of XQuery, and our favorite XQuery processor, DataDirect XQuery®.

Stay Informed!

XQuery is one of the hottest XML technologies being developed today. Stay informed with vital news about standards, tools, and trends by signing up for the DataDirect XQuery® newsletter.

DataDirect XQuery FAQ

This informative DataDirect XQuery® FAQ answers frequently-asked questions about DataDirect XQuery®, including questions about performance, scalability, use-cases, resources, and more.

If you're more of a hands-on learner, then download a free copy and start exploring DataDirect XQuery® today!

DataDirect XQuery® and Document Projection

Querying large — over one Gig! — XML documents is no problem for DataDirect XQuery®, which uses document projection and data streaming technologies to efficiently query XML. Read about it, and then try DataDirect XQuery® for free and see for yourself.

DataDirect XQuery® Features

With tools for accessing relational data as XML, XML publishing, and full XML IDE support, to name a just a few, DataDirect XQuery® is loaded with these and other features that will make building your next data integration application a snap.

Download your copy today and see for yourself!

Where is XQuery Heading

We've collected numerous reports and surveys to help you understand the lay of the XQuery land and how DataDirect XQuery® can help you launch your data integration applications to new heights.

From XML industry observers to surveys of your peers, see why DataDirect XQuery® is right for you. Or better yet, try DataDirect XQuery® today for free!

New Case Study

Gevity produces sales proposals in real time using DataDirect XQuery®. See how Gevity uses DataDirect XQuery® to combine Web service data from SalesForce.com with relational data in Oracle in a pricing engine for HR management.

Use DataDirect XQuery® to Mine Relational Data!

This informative XQuery webinar shows you how scalable, flexible, and reliable DataDirect XQuery® is for building XML applications that access data from relational databases, XML, Web services and legacy formats; and how it uses document projection and data streaming technologies to efficiently query large (Gigabytes) XML documents.

Check our XQuery Webinars page for more information on this and other Webinar topics.

Are You Ready for the XQuery Revolution?

XQuery is poised to change the data integration application landscape. This first-ever informative industry survey will let you know what your peers are up to and how they are putting XQUery to work today.

After reading the survey try DataDirect XQuery® for free and see for yourself.

Zapthink: Simplify Data Integration with DataDirect XQuery®

This report from industry observer Zapthink describes how using DataDirect XQuery® simplifies data integration using standards-based documents and general-purpose transformation engines.

Seeing is believing! Try DataDirect XQuery® today!.

DataDirect XQuery® for XML Publishing and More!

In this informative XQuery webinar, learn how XQuery and the XQuery API for Java (XQJ) can be used in Java applications for XML report writing, XML publishing, Web publishing, Web services, and XML data services.

Check our XQuery Webinars page for more information on this and other XQuery topics.

Query Relational Data with DataDirect XQuery

DataDirect XQuery® can access relational data as XML on Oracle, Sybase ASE, IBM DB2, and Microsoft SQL Server databases, to name a few.

Download a free copy and start exploring DataDirect XQuery® today!

Feeds

Multimedia

Newsletter