Using XQuery to Construct XML from Relational Data

Querying Data with Oracle XQuery

Article Index
Querying Data with Oracle XQuery
Using XQuery to Construct XML from Relational Data

Page 2 of 2

Using XQuery to Construct XML from Relational Data

In the preceding sections, you saw several examples of how to construct XML representations of relational data using the SQL/XML generation function, as well as using object types when creating XMLType views on relational tables. In this section, you will learn how to build XML on relational data with XQuery.

Turning back to the Using Oracle SQL/XML Generation Functions section, you might modify the listing containing the SQLXMLQuery.php script that uses PHP DOM extension functions to produce an XML representation of relational data to use XQuery instead of those functions, as shown overleaf:

<?php
//File: XQuery.php
if(!$rsConnection = oci_connect('hr', 'hr', '//localhost/orcl')) {
$err = oci_error();
trigger_error('Could not establish a connection: ' .
$err['message'], E_USER_ERROR);
};
$dept_id=90;
$query =
'SELECT XMLQuery('.
"'".'for $j in 1
return (
<EMPLOYEES> {
for $i in ora:view("hr", "employees")/ROW
where $i/DEPARTMENT_ID = $deptid
return (<EMPLOYEE id="{xs:integer($i/EMPLOYEE_ID)}">
<ENAME>{xs:string($i/LAST_NAME)}</ENAME>
<SALARY>{xs:integer($i/SALARY)}</SALARY>
</EMPLOYEE>)} </EMPLOYEES>)'."'".
'PASSING XMLElement("deptid", :deptid) AS "deptid"
RETURNING CONTENT).getStringVal() RESULT FROM DUAL';
$stmt = oci_parse($rsConnection,$query);
oci_bind_by_name($stmt, ':deptid', $dept_id);
if (!oci_execute($stmt)) {
$err = oci_error($stmt);
trigger_error('Query failed: ' . $err['message'], E_USER_ERROR);
}
$xmlDoc = oci_fetch_assoc($stmt);
$domxml = new DOMDocument();
$domxml->loadXML($xmlDoc['RESULT']);
print $domxml->saveXML();
?> 
   
%3C%3Fphp%0D%0A%2F%2FFile%3A%20XQuery.php%0D%0Aif%28%21%24rsConnection%20%3D%20oci_connect%28%27hr%27%2C%20%27hr%27%2C%20%27%2F%2Flocalhost%2Forcl%27%29%29%20%7B%0D%0A%24err%20%3D%20oci_error%28%29%3B%0D%0Atrigger_error%28%27Could%20not%20establish%20a%20connection%3A%20%27%20.%0D%0A%24err%5B%27message%27%5D%2C%20E_USER_ERROR%29%3B%0D%0A%7D%3B%0D%0A%24dept_id%3D90%3B%0D%0A%24query%20%3D%0D%0A%27SELECT%20XMLQuery%28%27.%0D%0A%22%27%22.%27for%20%24j%20in%201%0D%0Areturn%20%28%0D%0A%3CEMPLOYEES%3E%20%7B%0D%0Afor%20%24i%20in%20ora%3Aview%28%22hr%22%2C%20%22employees%22%29%2FROW%0D%0Awhere%20%24i%2FDEPARTMENT_ID%20%3D%20%24deptid%0D%0Areturn%20%28%3CEMPLOYEE%20id%3D%22%7Bxs%3Ainteger%28%24i%2FEMPLOYEE_ID%29%7D%22%3E%0D%0A%3CENAME%3E%7Bxs%3Astring%28%24i%2FLAST_NAME%29%7D%3C%2FENAME%3E%0D%0A%3CSALARY%3E%7Bxs%3Ainteger%28%24i%2FSALARY%29%7D%3C%2FSALARY%3E%0D%0A%3C%2FEMPLOYEE%3E%29%7D%20%3C%2FEMPLOYEES%3E%29%27.%22%27%22.%0D%0A%27PASSING%20XMLElement%28%22deptid%22%2C%20%3Adeptid%29%20AS%20%22deptid%22%0D%0ARETURNING%20CONTENT%29.getStringVal%28%29%20RESULT%20FROM%20DUAL%27%3B%0D%0A%24stmt%20%3D%20oci_parse%28%24rsConnection%2C%24query%29%3B%0D%0Aoci_bind_by_name%28%24stmt%2C%20%27%3Adeptid%27%2C%20%24dept_id%29%3B%0D%0Aif%20%28%21oci_execute%28%24stmt%29%29%20%7B%0D%0A%24err%20%3D%20oci_error%28%24stmt%29%3B%0D%0Atrigger_error%28%27Query%20failed%3A%20%27%20.%20%24err%5B%27message%27%5D%2C%20E_USER_ERROR%29%3B%0D%0A%7D%0D%0A%24xmlDoc%20%3D%20oci_fetch_assoc%28%24stmt%29%3B%0D%0A%24domxml%20%3D%20new%20DOMDocument%28%29%3B%0D%0A%24domxml-%3EloadXML%28%24xmlDoc%5B%27RESULT%27%5D%29%3B%0D%0Aprint%20%24domxml-%3EsaveXML%28%29%3B%0D%0A%3F%3E

The Oracle SQL function XMLQuery can be used to construct or query XML data, based on an XQuery expression passed as the parameter. In this example, you use the XMLQuery function to generate an XML representation of some data stored in the relational table hr.employees.

You start the FLWOR expression used in this example with the for clause performing only one iteration.

NOTE: FLWOR stands for for, let, where, order by, return?the clauses used when composing an XQuery expression.

Next, in the nested FLWOR expression that starts with the for clause, you iterate over the hr.employees rows selected based on the condition specifi ed in the where clause. With the help of the ora:view XQuery function, you query relational table hr.employees, as it were an XMLType table, creating XML documents on the fly.

In the return clause of the FLWOR expression, you construct the EMPLOYEE nodes of the resultant EMPLOYEES document.

You bind dynamic variable deptid to an XQuery expression using the PASSING clause. This variable is used in the where clause of the nested FLWOR expression, restricting the retrieved employee records to those that belong to the specified department.

When executed, the XQuery.php script shown in the listing opposite should produce the same XML document as the one shown in the Creating XML with the DOM PHP Extension section at the beginning of this chapter. If you want to print the resultant XML document in HTML format, you might perform an XSL transformation before outputting it. To achieve this, you might replace the last line of code in the script:

print $domxml->saveXML();

with the following lines:

$domxsl = new DOMDocument();
$domxsl->load('employees.xsl');
$proc = new XSLTProcessor;
$xsl = $proc->importStylesheet($domxsl);
$rslt = $proc->transformToXml($domxml);
print $rslt; 
   
%24domxsl%20%3D%20new%20DOMDocument%28%29%3B%0D%0A%24domxsl-%3Eload%28%27employees.xsl%27%29%3B%0D%0A%24proc%20%3D%20new%20XSLTProcessor%3B%0D%0A%24xsl%20%3D%20%24proc-%3EimportStylesheet%28%24domxsl%29%3B%0D%0A%24rslt%20%3D%20%24proc-%3EtransformToXml%28%24domxml%29%3B%0D%0Aprint%20%24rslt%3B

Assuming that you have created the employees.xsl stylesheet as discussed in the Transforming and Processing XML with XSLT section earlier in this chapter, with the above replacement, the XQuery.php script shown in the listing should produce the HTML table as shown in the Transforming and Processing XML with XSLT section earlier.

Using XQuery to Construct XML from Relational Data

Tags