XML-Enabled Applications | Page 3

XML-Enabled Applications

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.

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 file 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.

Moving All the XML Processing into the Database

In the preceding example, the database server performs only a part of the XML processing while the rest is still performed by the PHP engine. Specifically, the database server generates an employees XML document based on the records from the hr.employees table, and the PHP script then transforms that document with XSLT into HTML format with the PHP XSL extension functions.

As an efficient alternative to PHP's XSLT processor, you might use Oracle's XSLT processor, thus benefiting from performing XSL transformations inside the database.

The following figure depicts the scenario where both generating XML and then transforming it into HTML take place inside the database.

[ There are several advantages to performing XSLT transformations, as well as many other XML processing operations, inside the database. These advantages are outlined at the beginning of the Performing XML Processing inside the Database section earlier in this chapter. ]

The explanation of the steps in the figure is as follows:

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

However, before you implement this scenario, you have to decide where to store the XSL stylesheet to be used for the XSL transformation. Obviously, retrieving the stylesheet from the web server before performing the transformation on the database server would be a bad idea in this case, since it would increase network overhead. In contrast, storing t he stylesheet in the database would be the best solution for this situation.

When choosing the storage option for XSL stylesheets, you should bear in mind that an XSL stylesheet is in fact an XML document. So, it would be a good idea to choose one of the XML storage options available in Oracle database.

Storing XML Data in the Database

When using the database as a persistent storage for XML, you have several storage options. While all these options are discussed in the Database Storage Options for XML Data in Oracle Database section later in this chapter, this section provides a simple example of how you might store XML documents in an XMLType column in a database table as Character Large Object (CLOB) values. Once created, such a table can be used for storing different XML documents, including XSL stylesheets.

However, before creating this table you might want to create a new database schema. To create that schema and grant it all the required privileges, you might execute the SQL statements shown below:

Once the xmlusr schema is created and all the privileges required to work with it are granted, you can create the XSLTstylesheets table under this schema and populate it with the data. You might achieve this by issuing the SQL statements shown next:

CONN xmlusr/xmlusr
CREATE TABLE XSLTstylesheets (
  id NUMBER,
  stylesheet XMLType
);
INSERT INTO XSLTstylesheets VALUES (
  1,
  XMLType(
  '<?xml version="1.0" encoding="utf-8" ?>
    <xsl:stylesheet version="1.0" xmlns:xsl="https://www.w3.org/1999/XSL/Transform">
      <xsl:template match="/">
        <html>
          <head>
            <title>Employees</title>
          </head>
          <body>
            <font face="Arial">
            <h2>List of employees from employees.xml</h2>
            <table border="1" cellspacing="0" cellpadding="5">
            <tr>
              <th><b>EMPLOYEE ID</b></th>
              <th><b>LAST NAME</b></th>
              <th><b>SALARY</b></th>
            </tr>
              <xsl:for-each select="EMPLOYEES">
                <xsl:for-each select="EMPLOYEE">
                  <tr>
                    <td><xsl:value-of select="@id"/></td>
                    <td><xsl:value-of select="ENAME"/></td>
                    <td><xsl:value-of select="SALARY"/></td>
                  </tr>
                </xsl:for-each>
              </xsl:for-each>
            </table>
            </font>
          </body>
        </html>
      </xsl:template>
    </xsl:stylesheet>')
);
COMMIT;

As you can see, inserting a new row into a table that contains an XMLType column is similar to inserting a new row into any other tableÂyou use an INSERT statement and then issue the COMMIT to make the changes permanent. The only thing to notice here is that you have to explicitly convert the string representing an XML document to an XMLType value before inserting it to an XMLType column.

In this example, you insert only one row into the newly created XSLTstylesheets table. The stylesheet column of XMLType in this row includes the employees XSL stylesheet discussed in the Transforming and Processing XML with XSLT section earlier in this chapter. Once you have stored this stylesheet in the XSLTstylesheets table, you can access it with a SELECT statement when connected as xmlusr/xmlusr.

However, before you can move on to a script that will implement the scenario depicted in the figure shown in the Moving All the XML Processing into the Database section earlier in this chapter, you need to grant the SELECT privilege on the hr.employees table to the xmlusr database schema. This can be done by issuing the following statements from SQL*Plus:

By granting the SELECTprivilege on the hr.employees table to xmlusr you permit the applications that will connect to the database through this schema to access data stored in the table.

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.

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.

[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:

XML-Enabled Applications | Page 3