External Variables

XQJ prepared queries are analogous to SQL prepared statements; they use XQuery external variables in the same way that SQL uses parameter markers. The DataDirect XQuery SQL Adapter converts queries with external variables into SQL prepared statements (except when an external variable contains a sequence — this case is discussed in Example 11).

Example 10. XQuery External Variables

XQuery

declare variable $shares as xs:integer external;
for $h in collection('HOLDINGS')/HOLDINGS
where $h/SHARES < $shares
return $h/USERID

Generated SQL

SELECT ALL
 nrm4."USERID" AS RACOL1
FROM
 "PEPPINO"."HOLDINGS" nrm4
WHERE
 nrm4."SHARES" < ?

Example 11. Sequences as External Variables

XQuery supports sequences as external variables, but in SQL, a parameter is a single value. DataDirect XQuery uses temporary tables in the database to represent XQuery external variables that contain sequences. The sequence is inserted into the temporary table using batch insert in order to minimize the number of database round trips.

XQuery

declare variable $shares as xs:int* external;
for $h in collection('HOLDINGS')/HOLDINGS
where $h/SHARES = $shares
return $h/USERID

Generated SQL

INSERT INTO RATEMP009368710001( RAVAR, RAINT) VALUES(0, ?);


SELECT ALL
  nrm4."USERID" AS RACOL2
FROM
  "PEPPINO"."HOLDINGS" nrm4
WHERE
  EXISTS(SELECT ALL
           1 AS RACOL1
         FROM
           RATEMP009368710001 nrm7
         WHERE
           nrm7.RAVAR = 0 AND nrm4."SHARES" = nrm7.RAINT)
;


DELETE FROM RATEMP009368710001 WHERE RAVAR IN  (0)
Previous

Building XML Hierarchies 		HomeNext

XQuery Global Variables