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>  

USING XQUERY

Visit the DataDirect Resource Library

Ready To Learn More?

Be sure to visit the Progress DataDirect Resource Library regularly because we are continually adding new tutorials.