Joining Data from XML and Relational Sources

---

This tutorial has already explored how XQJ allows XQuery to operate on relational and XML file data stores. Now let's leverage that functionality to query both types of data stores at the same time using a single query.

In this example, we use a Web Service request to provide parameters for the query, and then query a database to create the Web Service response. The Web Service request looks like this:

<request> 
    <performance> 
        <UserId>Jonathan</UserId> 
        <start>2003-01-01</start> 
        <end>2003-01-01</end> 
    </performance> 
</request> 

This request contains only the SOAP message payload. (To simplify the example, the envelope has been omitted.) This request asks for performance data on a user's portfolio within a specific date range.

Now we can compose a query that uses the parameters from the request to create a performance report, which will report the performance of each stock held by each user during the given range.

let $request := doc("request.xml")/request 
for $user in $request/performance 
return 
   <portfolio UserID="{$user/UserId}"> 
     { $request } 
     {  
       for $h in collection("holdings")/holdings 
       where $h/userid = $user/UserId 
       return       
         <stock> 
          { 
           $h/stockticker, 
           $h/shares 
          } 
         </stock> 
     } 
   </portfolio> 

First, establish a connection to the data source.

XQConnection conn = ds.getConnection(); 

Create an XQExpression object that executes the XQuery expression and returns a sequence of results.

FileReader fileReader = new FileReader("flwor.xq"); 
XQExpression xqExpression = conn.createExpression(); 
XQSequence xqSequence = xqExpression.executeQuery(fileReader); 

With the query results in a sequence, serialize this sequence using the getSequenceAsString() method.

System.out.println(xqSequence.getSequenceAsString()); 

The result looks like this (whitespace has been modified for readability):

<portfolio UserID="Jonathan"> 
   <request> 
      <performance> 
         <UserId>Jonathan</UserId> 
         <start>2003-01-01</start> 
         <end>2004-06-01</end> 
      </performance> 
   </request> 
      <stock> 
         <stockticker>PRGS</stockticker> 
         <shares>23</shares> 
      </stock> 
      <stock> 
         <stockticker>AMZN</stockticker> 
         <shares>3000</shares> 
      </stock> 
      <stock> 
         <stockticker>EBAY</stockticker> 
         <shares>4000</shares> 
      </stock> 
      <stock> 
         <stockticker>IBM</stockticker> 
         <shares>2500</shares> 
         <shares>2500</shares> 
      </stock> 
</portfolio> 

Other similar examples can be found in the XQJExecute example.