alt
Advertisement
Online Training
Career Series
Exforsys
Exforsys arrow Tutorials arrow PHP Oracle arrow Moving All the XML Processing into the Database
Site Search


Moving All the XML Processing into the Database

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.

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

CONN /as sysdba
CREATE USER xmlusr IDENTIFIED BY xmlusr;
GRANT connect, resource TO xmlusr;

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:

  1. CONN xmlusr/xmlusr
  2.  
  3. CREATE TABLE XSLTstylesheets (
  4.    id NUMBER,
  5.    stylesheet XMLType
  6. );
  7.  
  8. INSERT INTO XSLTstylesheets VALUES (
  9. 1,
  10. XMLType(
  11. '<?xml version="1.0" encoding="utf-8" ?>
  12. <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/
  13. Transform">
  14.    <xsl:template match="/">
  15.     <html>
  16.      <head>
  17.       <title>Employees</title>
  18.      </head>
  19.      <body>
  20.       <font face="Arial">
  21.       <h2>List of employees from employees.xml</h2>
  22.       <table border="1" cellspacing="0" cellpadding="5">
  23.       <tr>
  24.            <th><b>EMPLOYEE ID</b></th>
  25.            <th><b>LAST NAME</b></th>
  26.            <th><b>SALARY</b></th>
  27.       </tr>
  28.       <xsl:for-each select="EMPLOYEES">
  29.            <xsl:for-each select="EMPLOYEE">
  30.            <tr>
  31.                 <td><xsl:value-of select="@id"/></td>
  32.                 <td><xsl:value-of select="ENAME"/></td>
  33.                 <td><xsl:value-of select="SALARY"/></td>
  34.            </tr>
  35.          </xsl:for-each>
  36.           </xsl:for-each>
  37.        </table>
  38.       </font>
  39.       </body>
  40.      </html>
  41.     </xsl:template>
  42.    </xsl:stylesheet>')
  43.   );
  44. 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:

CONN /as sysdba
GRANT SELECT ON hr.employees TO xmlusr;

By granting the SELECT privilege 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.

Tags

See All Tags

Trackback(0)
Comments (0)add comment

Write comment

busy
 
< Prev   Next >
[ Back ]
Sponsored Links
© 2008 Exforsys.com
Joomla! is Free Software released under the GNU/GPL License.
Page copy protected against web site content infringement by Copyscape