Example 1: Using XQuery Result Sequences and JDBC Prepared Statements

For each customer element, this application accesses the custinfo table and customer child elements using an XQuery query. Then, the application determines whether the current customer element triggers an insert, update, or delete of the custinfo table.

(: Input XML message is passed using an external variable :)
declare variable 
 $inp as document-node(element(*,xdt:untyped)) external; 

(: Begin XQuery query :)
(: Iterate over customer elements :)
for $x in $inp/customers/customer 

(: Extract customer information :)
let $custid := xs:int($x/@id) 
let $firstname := xs:string($x/name/first) 
let $lastname := xs:string($x/name/last) 
let $address := xs:string($x/address) 

(: Access custinfo with $custid and decide on action :)
let $isUpdate := 
 exists(($firstname,$lastname,$address)) and
 exists(collection('custinfo')//custid[. eq $custid]) 
let $isDelete := 
 not($isUpdate) and 
 exists(collection('custinfo')//custid[. eq $custid]) 
let $isInsert := 
 not($isUpdate) and not($isDelete) 

(: Return action :)
return 
 if ($isUpdate) 
 then 
 ... (: tell caller to update custinfo :)
 else 
 if ($isInsert) 
 then 
 ... (: tell caller to insert new row into custinfo :)
 else 
 ... (: tell caller to delete row from custinfo :)

Depending on the type of operation that is required, the XQuery query returns one of the following strings: 'insert', 'update', or 'delete'. Then, the query appends to that string the information required to execute the data modification (for example, $custid is appended to 'delete').

Notice that we use the sep element to differentiate the separate parts of the result. This makes it easy to skip any child element that is missing from the XML message.

...
 if($isUpdate) 
 then (
 'update',
 $custid,<sep/>,
 $firstname,<sep/>,>
 $lastname,<sep/>,
 $address,<sep/>)
 else 
 if($isInsert) 
 then (
 'insert',
  $custid,<sep/>,
  $firstname,<sep/>,
  $lastname,<sep/>,
  $address,<sep/>) 
 else 
 ('delete',$custid,<sep/>)

Using the following JDBC prepared statements, which are created and prepared before the XQuery query is executed, the application updates the custinfo table with the information returned from the query.

update=jdbcCon.prepareStatement(
 "update custinfo set firstname=?,lastname=?,address=? where custid=?");

insert=jdbcCon.prepareStatement
 ("insert into custinfo values(?,?,?,?)");

delete=jdbcCon.prepareStatement(
 "delete from custinfo where custid=?");

While iterating over the results of the query, a JDBC parameter batch is added to the appropriate JDBC prepared statements for each customer element that exists in the XML message. After the query is executed and all its results are processed, the JDBC prepared statements are executed to update the custinfo table.

The application executes the XQuery query and iterates over the results. It retrieves the first item, and depending on whether the string is 'insert', 'update', or 'delete', it retrieves the additional data associated with that type of update. Finally, it adds this information to the appropriate JDBC prepared statements using JDBC setXX() methods and the JDBC addBatch() method. After the complete XQuery result sequence has been processed, the three prepared statements are executed.