XML-Enabled Applications - Part 4/Page 3

[previous] [next]

XML-Enabled Applications - Part 4

Querying Data with Oracle XQuery

Starting with Oracle Database 10g Release 2, you can take advantage of a full-featured native XQuery engine integrated with the database. With Oracle XQuery, you can accomplish various tasks involved in developing PHP/Oracle XML applications, operating on any kind of data that can be expressed in XML.

The following figure shows the data sources with which Oracle XQuery can work.

As you can see from the figure, XQuery can be used to query any data stored in the database and out of it.

Please note that XQuery is not available in the Express Edition of Oracle Database. For more information on Oracle XQuery, you can refer to Oracle documentation: chapter Using XQuery with Oracle XML DB in the Oracle XML DB Developer's Guide. You can also refer to the XQuery 1.0: An XML Query Language W3C Recommendation.

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(); ?>

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 forclause performing only one iteration.

[ FLWOR stands for for, let, where, orderby, 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 specified 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 deptidto 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;

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.

Breaking up XML into Relational Data

While the preceding example shows how to construct an XML representation over relational data, the example in this section illustrates how you can shred XML data back into relational data. This reverse operation can be useful if your application works with relational data rather than XML, but the data which you work with, is stored in XML format.

Turning back to the employees XMLType table generated during the registration of the employee.xsd XML schema discussed in the Using XML Schemas section, you might use the SQL function XMLTable to shred the employee XML documents into individual columns of a virtual table, as shown below:

CONN xmlusr/xmlusr SELECT emps.empno,emps.ename, emps.salary FROM employees, XMLTable( 'for $i in /EMPLOYEE return $i' PASSING OBJECT_VALUE COLUMNS empno NUMBER PATH '/EMPLOYEE/@id', ename VARCHAR2(30) PATH '/EMPLOYEE/ENAME', salary NUMBER PATH '/EMPLOYEE/SALARY') emps;

Assuming that you have followed the instructions in the preceding sections, the output generated by the query shown in the listing might look like this:

EMPNO ENAME SALARY ----- ------------------------------ ---------- 100 King 24000 101 Horka 25000 303 Locke 7000 304 Polonski 8200

In a real-world situation, in order to hide data complexity, you might find it useful to build a view on the query shown in the listing, so that the people designing SQL queries against that view have no idea they are dealing with data actually stored in XML format.

Summary

When building XML-enabled PHP/Oracle applications, the database can not only be used as an efficient means for storing XML data, but also to operate on any kind of data that can be expressed in XML.

In this chapter you learned how to use XML techniques and technologies available in PHP and Oracle when building XML-enabled PHP/Oracle applications. In particular, you saw how to use PHP's XML extensions and how to take advantage of Oracle XML DB, a set of Oracle XML technologies making Oracle Database an ideal choice for data-driven XML applications.

By now you should have a good understanding of how you can use XML techniques and technologies available in PHP and Oracle to build robust XML-enabled PHP applications on Oracle Database. Armed with this knowledge, you will be able to understand better XML Web Services discussed in the next chapter.

[This is an escerpt from the book, PHP Oracle Web Development: Data processing, Security, Caching, XML, Web Services, and Ajax, by Yuli Vasiliev. Published by Packt Publishing Ltd., 2007

Digg This Add to del.icio.us

[previous] [next]

URL: