Performing XML Processing inside the Database

Category: PHP Oracle
Comments (0)

Performing XML Processing inside the Database

When building XML-enabled applications on top of Oracle, there are many advantages to performing the XML processing inside the database when compared to performing it on the client. The key advantages to perform XML processing inside the database are as follows:

Benefiting from the XML-specific memory optimizations provided by Oracle XML DB
Eliminating overhead associated with parsing XML documents
Reducing overhead associated with I/O disk operations and network traffic between the Web server and database server

Moving XML processing to the database may be especially useful if you are dealing with large XML documents stored in the database. In that case, your application won't need to transfer a large amount of data between the database and web server when processing XML inside the database?only the final product is sent across the wire.

Using Oracle SQL/XML Generation Functions

The simplest way to benefit from moving XML processing to the database is to use Oracle SQL/XML functions, which allow you to build SQL queries generating XML from relational data.

Turning back to the preceding sample, you might, for example, rewrite the query issued against the database so that it retrieves the generated employees XML document that is ready to be transformed into HTML with the PHP XSL extension functions.

Diagrammatically, this might look like the following figure:

The explanation of the steps in the figure is the following:

Step 1: The script issues the query containing SQL/XML functions so that it retrieves an XML document generated by the database server.
Step 2: The database server generates the XML document, based on the query issued by the script in step 1.
Step 3: The script transforms the XML document retrieved from the database into HTML format with the help of the PHP XSL extension functions.
Step 4: The script posts the HTML page generated in step 3 to the user's browser.

In this scenario, you move some XML processing from the web server to the database server. In particular, the XML document is now generated on the database server with the help of the SQL/XML generation functions specified in the query, rather than generating that document on the web server with the PHP DOM extension functions as it was in the scenario depicted in the figure shown in the Transforming and Processing XML with XSLT section earlier in this chapter.

The following listing contains the SQLXMLQuery.php script that implements the above scenario. So, the script issues the query that makes Oracle generate the employees XML document, thus retrieving the employees XML document that is ready to be transformed with XSLT. The following script provides an example of using Oracle SQL/XML functions to generate XML from relational data. Using these functions lets you move the processing required to generate the employees XML document from the web server to the database server.

Sample Code

<?php
//File: SQLXMLQuery.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 XMLELEMENT("EMPLOYEES",
XMLAgg(
XMLELEMENT("EMPLOYEE",
XMLATTRIBUTES(employee_id AS "id"),
XMLFOREST(last_name as "ENAME", salary as "SALARY"))))
AS result
FROM employees WHERE department_id=:deptid';
$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']);
$domxsl = new DOMDocument();
$domxsl->load('employees.xsl');
$proc = new XSLTProcessor;
$xsl = $proc->importStylesheet($domxsl);
$rslt = $proc->transformToXml($domxml);
print $rslt;
?>

As you can see, the SQLXMLQuery.php script, unlike the DOM.php script discussed earlier in this chapter, does not use the PHP DOM functions to generate the employees XML document from scratch, based on the result set retrieved from the database. Instead, it issues a query that instructs the database server to generate that XML document. After executing the query, you fetch the result of the query and then load it to the newly created DOM document.

Next, you load the employees.xsl XSL stylesheet discussed in the Transforming and Processing XML with XSLT section earlier, assuming that this fi le resides in the same directory where you saved the SQLXMLQuery.php script discussed here.

Then, you create an XSLT processor, in which you import the employees.xsl stylesheet loaded into a DOM document. After performing the XSL transformation, you print the resultant HTML page.

When you run the SQLXMLQuery.php script, it should output a page that looks like the one shown in the figure in the Transforming and Processing XML with XSLT section.

Sponsored Links

PHP Oracle Tutorials

Performing XML Processing inside the Database

Performing XML Processing inside the Database

Using Oracle SQL/XML Generation Functions

Comments

Post Your Comment:

Sponsored Links