Performing XSLT Transformations inside Oracle Database

Performing XSLT Transformations inside the Database

Now that you have the employees XSL stylesheet stored in the database and the xmlusr schema is permitted to access the hr.employees table, you can create a script that will instruct the database to build an HTML page based on the data stored in hr.employees.

The following listing contains the source code for such a script.

<?php
   //File: DBServerXSLTrans.php
   if(!$rsConnection = oci_connect('xmlusr', 'xmlusr',
            '//localhost/orcl')) {
      $err = oci_error();
      trigger_error('Could not establish a connection:
      '.$err['message'], E_USER_ERROR);
   };
   $dept_id = 90;
   $query = 'SELECT XMLtransform(x.xmlcol,
   (SELECT stylesheet FROM XSLTstylesheets WHERE
      id = 1)).getStringVal()
   AS result FROM
   (SELECT XMLELEMENT("EMPLOYEES",
      XMLAgg(
      XMLELEMENT("EMPLOYEE",
      XMLATTRIBUTES(employee_id AS "id"),
      XMLFOREST(last_name AS "ENAME", salary AS "SALARY")
       )
      )
   ) AS xmlcol
   FROM hr.employees WHERE department_id=:deptid) x';
   $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);
   $dom = new DOMDocument();
   $dom->loadXML($xmlDoc['RESULT']);
   echo $dom->saveXML();
   ?> 
   
%3C%3Fphp%0D%0A%26nbsp%3B%20%26nbsp%3B%2F%2FFile%3A%20DBServerXSLTrans.php%0D%0A%26nbsp%3B%20%26nbsp%3Bif%28%21%24rsConnection%20%3D%20oci_connect%28%27xmlusr%27%2C%20%27xmlusr%27%2C%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%27%2F%2Flocalhost%2Forcl%27%29%29%20%7B%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%24err%20%3D%20oci_error%28%29%3B%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3Btrigger_error%28%27Could%20not%20establish%20a%20connection%3A%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%27.%24err%5B%27message%27%5D%2C%20E_USER_ERROR%29%3B%0D%0A%26nbsp%3B%20%26nbsp%3B%7D%3B%0D%0A%26nbsp%3B%20%26nbsp%3B%24dept_id%20%3D%2090%3B%0D%0A%26nbsp%3B%20%26nbsp%3B%24query%20%3D%20%27SELECT%20XMLtransform%28x.xmlcol%2C%0D%0A%26nbsp%3B%20%26nbsp%3B%28SELECT%20stylesheet%20FROM%20XSLTstylesheets%20WHERE%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3Bid%20%3D%201%29%29.getStringVal%28%29%0D%0A%26nbsp%3B%20%26nbsp%3BAS%20result%20FROM%0D%0A%26nbsp%3B%20%26nbsp%3B%28SELECT%20XMLELEMENT%28%22EMPLOYEES%22%2C%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3BXMLAgg%28%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3BXMLELEMENT%28%22EMPLOYEE%22%2C%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3BXMLATTRIBUTES%28employee_id%20AS%20%22id%22%29%2C%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3BXMLFOREST%28last_name%20AS%20%22ENAME%22%2C%20salary%20AS%20%22SALARY%22%29%0D%0A%26nbsp%3B%20%26nbsp%3B%20%20%20%20%29%0D%0A%26nbsp%3B%20%26nbsp%3B%20%20%20%29%0D%0A%26nbsp%3B%20%26nbsp%3B%29%20AS%20xmlcol%0D%0A%26nbsp%3B%20%26nbsp%3BFROM%20hr.employees%20WHERE%20department_id%3D%3Adeptid%29%20x%27%3B%0D%0A%26nbsp%3B%20%26nbsp%3B%24stmt%20%3D%20oci_parse%28%24rsConnection%2C%24query%29%3B%0D%0A%26nbsp%3B%20%26nbsp%3Boci_bind_by_name%28%24stmt%2C%20%27%3Adeptid%27%2C%20%24dept_id%29%3B%0D%0A%26nbsp%3B%20%26nbsp%3Bif%20%28%21oci_execute%28%24stmt%29%29%20%7B%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%24err%20%3D%20oci_error%28%24stmt%29%3B%0D%0A%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%26nbsp%3Btrigger_error%28%27Query%20failed%3A%20%27.%24err%5B%27message%27%5D%2C%20E_USER_ERROR%29%3B%0D%0A%26nbsp%3B%20%26nbsp%3B%7D%26nbsp%3B%20%26nbsp%3B%0D%0A%26nbsp%3B%20%26nbsp%3B%24xmlDoc%20%3D%20oci_fetch_assoc%28%24stmt%29%3B%0D%0A%26nbsp%3B%20%26nbsp%3B%24dom%20%3D%20new%20DOMDocument%28%29%3B%0D%0A%26nbsp%3B%20%26nbsp%3B%24dom-%3EloadXML%28%24xmlDoc%5B%27RESULT%27%5D%29%3B%0D%0A%26nbsp%3B%20%26nbsp%3Becho%20%24dom-%3EsaveXML%28%29%3B%0D%0A%26nbsp%3B%20%26nbsp%3B%3F%3E

As you can see, the select list of the SELECT statement used in the DBServerXSLTrans.php script includes the XMLtransform SQL/XML function. This function is used here to apply the employees XSL stylesheet retrieved from the XSLTstylesheets table by the subquery to the employees XML document generated by the subquery defined in the FROM clause of the query. The result of this transformation should be an HTML page, which you load into a new DOMDocument object and then display it in the browser. When displayed, the generated HTML page should look like the figure shown in the Transforming and Processing XML with XSLT section shown earlier in this chapter.

Building PHP Applications on Oracle XML DB

The preceding example shows how you might move the XML processing performed by your PHP/Oracle application from PHP to Oracle, thus taking advantage of the optimizations provided by the Oracle database server. In particular, you saw how to generate an XML document from scratch and apply an XSL transformation inside the database, rather than performing these operations with PHP.

In fact, Oracle XML Database provides much more functionality than what the sample demonstrates.

NOTE: Oracle XML DB refers to the set of Oracle Database XML technologies integrated with the relational database server, providing high performance XML storage, retrieval, and processing.

The most significant features of Oracle XML DB, which make Oracle database ideal for XML-enabled database-driven applications, are listed below:

Ability to store, retrieve, update, and transform XML data through the SQL and PL/SQL interfaces.
Ability to perform XML operations on SQL data without physically migrating it into XML format.
Oracle XML DB repository lets you manipulate XML content stored in the database with the standard Internet protocols, such as FTP, HTTP, and WebDAV.
Support for the Worldwide Web Consortium (W3C) XML Schema Recommendation: http://www.w3.org/TR/xmlschema-0/, allowing you to validate XML documents against appropriate XML schemas registered in the database.
XML-specific optimizations, reducing the cost of performing XML processing inside the database.

The subsections that follow show how you can make use of these features when building XML-enabled PHP/Oracle applications.

Performing XSLT Transformations inside the Database

Building PHP Applications on Oracle XML DB

Tags