SQLXML: Select into a variable for use in other queries

This article describes how to store the result of one SQL query into a variable and then use that result as part of a subsequent query.  For example, you may issue a Select statement to get the ID of a patient, and then use that patient ID as part of an Insert statement into another table.  The following SQLXML does that through the use of SQLXML variables and the Iterate element.

<?xml version=”1.0″ encoding=”UTF-8″?>

<sql:SQLXML xmlns:sql=”http://pilotfish.sqlxml”>

<!– Store the result set into a variable named “results” –>

<sql:Select into=”results”>

<PATIENT>

<PATIENT_ID />

<FIRST_NAME key=”true”>John</FIRST_NAME>

<LAST_NAME key=”true”>Doe</LAST_NAME>

</PATIENT>

</sql:Select>

<!– Loop through each row in the “results” record set.  The variable “result” will contain the current row of the results –>

<sql:Iterate over=”results” as=”result”>     

<sql:Insert>

<MATCHES>

<!– Use the value of the PATIENT_ID field in the current result –>

<MATCHED_PATIENT_ID>ognl:#result.getFieldValue(‘PATIENT_ID’)</MATCHED_PATIENT_ID>

</MATCHES>

</sql:Insert>

<sql:XMLOut var=”result” />

</sql:Iterate>

</sql:SQLXML>

Please refer to the SQLXML User’s Guide for more information.