Oracle XSQL Pages and the XSQL Servlet

Release Notes for Version 9.2.0.6 (Production)

Contents

Note :  Before installing XSQL Pages on a production server, please read the section below on Security Considerations for Production XSQL Pages System

Overview

SQL is the standard you are already familiar with for accessing appropriate views of business information in your production systems. XML provides an industry-standard, platform-neutral format for representing the results of SQL queries as "datagrams" for exchange, and XSLT defines the industry-standard way to transform XML "datagrams" into target XML, HTML, or Text formats as needed.

By combining the power of SQL, XML, and XSLT in the server with the ubiquitously available HTTP protocol for the transport mechanism you can:

Receive web-based information requests from any client device on the Web,

Query an appropriate logical "view" of business data needed by the request,

Return the "datagram" in XML over the web to the requester, or optionally

Transform the information flexibly into any XML, HTML, or text format they require.

What are XSQL Pages?

Assemble dynamic XML "datapages" based on one or more parametrized SQL queries, and

Transform the "datapage" to produce a final result in any desired XML, HTML, or Text-based format using an associated XSLT Transformation.

Make simple things very simple and hard things possible

Keep the "datapage" cleanly separate from the way that data will be rendered to the requester.

For example, to serve a list of available flights today for any desired destination city from your enterprise database in response to a URL request like:

http://yourcompany.com/AvailableFlightsToday.xsql?City=NYC

<?xml version="1.0"?>
<xsql:query connection="demo" bind-params="City" xmlns:xsql="urn:oracle-xsql">

    SELECT Carrier, FlightNumber, Origin, TO_CHAR(ExpectedTime,'HH24:MI') Due
      FROM FlightSchedule
     WHERE TRUNC(ArrivalTime) = TRUNC(SYSDATE)
       AND Destination = ?
  ORDER BY ExpectedTime

</xsql:query>

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="FlightList.xsl"?>
<xsql:query connection="demo" bind-params="City" xmlns:xsql="urn:oracle-xsql">

    SELECT Carrier, FlightNumber, Origin, TO_CHAR(ExpectedTime,'HH24:MI') Due
      FROM FlightSchedule
     WHERE TRUNC(ArrivalTime) = TRUNC(SYSDATE)
       AND Destination = ?
  ORDER BY ExpectedTime

</xsql:query>

Requesting the pages over the Web from browsers or client programs after installing the Oracle XSQL Servlet on your favorite Web Server,

Using the Oracle XSQL Command Line Utility in batch programs,

Calling the XSQLRequest.process() method from within any Java program

<jsp:include>ing an XSQL Page into your JSP page, or <jsp:forward>ing to

Using XSQL Pages, the information you already have in your Oracle database, and the power of SQL, XML, and XSLT, you'll quickly discover that a powerful and flexible world of web data publishing is at your fingertips.

Release 9.0.2

Bugs Fixed

In XSQL, Cookies contains Garbage data (Bug# 2954582)

New behavior introduced for parameters, session parameters, or cookies that have names which do not make legal XML names. Instead of generating an element <paramName> paramValue </paramName>, it will now generate an element named <xsql-illegal-name name="paramNameWithIllegalChars">paramValues</xsql-illegal-name>

XSQL-018: NullPointer starting up stylesheet pool under load  (Bug# 2661849)

When a user modified the default seting of 1 initial stylesheet in the pool, say putting 50, and then: (1) Restarts the servlet container or (2) Sends multiple, simultaneous requests to the XSQL page engine, then while the first thread to come in is initializing the sylesheet, another thread can sneak in and end up getting a null pointer exception while the other is still working to flesh out the pool instances.

<xsql:insert-param> Continues Incorrectly On Parse Failure

If the XML document posted in a request parameter was ill-formed, the processing of the insert incorrectly continued. Now a parse error stops processing as it should.

<xsql:include-xsql reparse="yes"> Fails if Document Refers to DTD

If the XML document posted in a request parameter was ill-formed, the processing of the insert incorrectly continued. Now a parse error stops processing as it should.

XSQL Connection Pools Not Cleaned Up When Servlet Destroyed

Some web servers allow the server administrator to shut down servlets and restart them without ever shutting down the Java VM that is running them. Under this scenario, the XSQL connection pool connections were not being cleaned up correctly. Now, when the servlet container calls destroy() on the XSQLServlet, all of the connection pools will be properly cleaned-up and all of the JDBC connection in the pools will be closed. Custom connection managers can implement the new oracle.xml.xsql.XSQLConnectionManagerCleanup interface to support this new cleanup behavior.

Servlet Continues Incorrectly After Reporting Error with XSQLConfig.xml

If the XSQLConfig.xml file is malformed or cannot be found, the XSQL Servlet reported a helpful error reporting this problem, but then incorrectly continued handling requests as if it had read a valid config file. This could lead to NullPointerException errors if database connections

XML Insert Demo Inserts Data with Leading/Trailing Spaces

The Insert XML Demo web page inadvertently contained leading and trailing spaces around the sample XML data to be inserted. These spaces have been removed.

Page, Session Parameters with Multibyte Values Handled Incorrectly

Prior to this fix, an attempt to assign a multibyte value to a page-private parameter or a session parameter resulted in the value being handled incorrectly. HTTP request parameters with multibyte values worked correctly before.

Note :  Since HTTP header entries are govered by the RFC822 standard (http://www.faqs.org/rfcs/rfc822.html, Section 3.2) that restricts headers to characters in the ISO 8859-1 character set, setting HTTP cookies to multibyte values is not guaranteed to work correctly.

Null Pointer Exception Using Tomcat When Relative URL Differs by Case

When running XSQL Pages using the Apache Tomcat servlet engine, an XSQL page that refers to an XSL stylesheet with a name like SOMETHING.xsl would cause a NullPointerException to be thrown if the actual file on disk exists but differs in case from the file being request. That is, if the file on disk is SomeThing.xsl, the Tomcat Server would complain by returning a null. Now an appropriate error message is displayed, encouraging you to check the case of the file.

Actions Removed by User-Written Action Handlers Still Executed

Prior to this fix, if a user-written action handler removed nodes from the XSQL page (typically ones nested inside the <xsql:action>element itself) those actions were executed anyway. Now, actions removed from the page which have not yet been processed will not be executed.

Custom Action Handlers Cannot Be Found in a Different JAR file on OC4J

Prior to this fix, if you are using Oracle9iAS OC4J to run XSQL pages, under certain circumstances the server would fail to find your action handler classes. Now the classes are found correctly with OC4J in all cases.

Illegal State Exception Posting XML to XSQL Page on OC4J

Prior to this fix, if you were posting an XML document to an XSQL page running under Oracle9iAS OC4J, an Illegal State Exception may result. This error no longer occurs.

New Features

New Performance Improvement Option for <xsql:include-owa>

The implementation of the built-in <xsql:include-owa> action handler has been improved to allow retrieving the entire OWA page buffer in a single network roundtrip by marshalling the contents of the page buffer using an Oracle8 TABLE type. Using this type-marshalling technique avoids the use of temporary CLOBs and gives better performance. In order to take advantage of the new feature, there are two steps:

Change the default OWA fetch style setting in your XSQLConfig.xml file to use TABLE instead of CLOB (the default).

<XSQLConfig>
  <processor>
    :
    <!--
     | Sets the default OWA Page Buffer fetch style
     | used by the <xsql:include-owa> action
     | Valid values are CLOB or TABLE.
     |
     | If set to CLOB, the processor uses temporary
     | CLOB to retrieve the OWA page buffer.
     |
     | If set to TABLE the processor uses a more
     | efficient approach that requires the existence
     | of the Oracle user-defined type named
     | XSQL_OWA_ARRAY defined using the DDL statement:
     |
     |  CREATE TYPE xsql_owa_array AS TABLE OF VARCHAR2(32767)
     |
     +-->
    <owa>
      <fetch-style>TABLE</fetch-style>
    </owa>
  </processor>
</XSQLConfig>

Create the XSQL_OWA_ARRAY type in the database account to which XSQL is connecting, using the DDL syntax:

CREATE TYPE xsql_owa_array AS TABLE OF VARCHAR2(32767);

Note :  If the default OWA fetch style is set to TABLE in the XSQLConfig.xml file, but you have failed to follow step 2 above and create the necessary type, the system falls back to using the default temporary CLOB approach, but this fallback scheme carries an overhead on each page request, so it's best to make sure you've created the type appropriately.

The value of the xpath attribute can be any valid XPath expression, optionally built using XSQL parameters as part of the attribute value like any other XSQL action element. Once a page-private parameter is set, subsequent action handlers can use this value as a lexical parameter ({@po_id}) or as a SQL bind parameter value by referencing its name in the bind-params attribute of any action handler that supports SQL operations.

<xsql:set-page-param> Now Supports xpath="Expr" Attribute

It is now possible to set the value of a page-private parameter to the value of an XPath expression, evaluated against an XML document or HTML form that has been posted to the XSQL Page Processor. The syntax is:

<xsql:set-page-param name="po_id" xpath="/technology/PurchaseOrder/Id"/>

The value of the xpath attribute can be any valid XPath expression, optionally built using XSQL parameters as part of the attribute value like any other XSQL action element. Once a page-private parameter is set, subsequent action handlers can use this value as a lexical parameter ({@po_id}) or as a SQL bind parameter value by referencing its name in the bind-params attribute of any action handler that supports SQL operations.

New Airport SOAP Service Demo

A variant on the existing Airport code lookup demo is provided that illustrates how you can use XSQL pages to create SOAP-based Web Services. The demo uses the new feature above to select XPath expressions out of the posted XML, and uses an XSLT stylesheet to format the response in the appropriate SOAP encoding.

Note :  You will need to edit the <soap:address> element's location attribute value in the AirportService.wsdl file to be appropriate for the environment where you deploy the XSQL demos.

Simplified Inclusion of XML from CLOB's and VARCHAR2 Columns

The existing <xsql:include-xml> action element now supports the ability to include a SQL select statement inside the action element content body like this:

<xsql:include-xml bind-params="id">
    select x.document.contents
      from xmldoc x
      where docid = ?
</xsql:include-xml>

When the action element has a SQL statement in its content, then a connection is required on the page. Only a single row is fetched from the SQL query, and the value of the first column in the result is retrieved and parsed. Both CLOB-valued and VARCHAR2-valued columns or SQL expressions are supported.

The existing functionality of <xsql:include-xml> to include XML from a URL still exists, and has the same syntax as before:

<xsql:include-xml href="someUrl"/>

New <xsql:include-posted-xml> Action to Include Posted XML

You can use the new <xsql:include-posted-xml> action element to include the XML document posted to an XSQL page. The element has no attributes, and the syntax is just:

<xsql:include-posted-xml/>

Support for Apache FOP 0.18 Release

The XSQL FOP Serializer, for producing PDF output from XSQL pages, has been tested with release 0.18.1 of Apache FOP. Due to an API change in the Apache FOP code, this release can only work with the 0.16 FOP release or greater, but not with earlier releases.

Note :  The implementation of the XSQLFOPSerializer depends on DOM2 API's and therefore must be used with the Oracle XML Parser 9.0.1.0.0, which supports DOM2. Attempting to use XSQL's FOP serialization with XML Parser 2.0.2.10 or earlier will result in an error message indicating a failure to load the oracle.xml.xsql.serializers.XSQLFOPSerializer class.

New Online Documentation for XSQL

The XSQL chapter in the "Oracle 9i Application Developer's Guide - XML" has been dramatically improved to feature all of the reference and tutorial material that formerly was only the these release notes. This rewritten chapter, entitled "XSQL Pages Publishing Framework", appears on Oracle Technet as part of the Oracle9i documentation set.

Support for Apache FOP 0.19.0

If you need to render PDF output from XSQL pages, this release supports and requires working with the 0.19.0 release of Apache FOP. The source code for the FOP Serializer looks like this:

package oracle.xml.xsql.serializers;
import org.w3c.dom.Document;
import java.io.PrintWriter;
import oracle.xml.xsql.*;
import org.apache.fop.messaging.MessageHandler;
import org.apache.fop.apps.*;
import java.io.*;
// ----------------------------------
// Tested with the FOP 0.19.0 Release
// ----------------------------------
public class XSQLFOPSerializer implements XSQLDocumentSerializer {
  private static final String PDFMIME = "application/pdf";
  public void serialize(Document doc, XSQLPageRequest env) throws Throwable {
    try {
      // First make sure we can load the driver
      Driver FOPDriver = new Driver();
      // Then set the content type before getting the reader/
      env.setContentType(PDFMIME);
      // Tell FOP not to spit out any messages.
      MessageHandler.setOutputMethod(MessageHandler.NONE);
      FOPDriver.setupDefaultMappings();
      FOPDriver.buildFOTree(doc);
      FOPDriver.setOutputStream(env.getOutputStream());
      FOPDriver.setRenderer(FOPDriver.RENDER_PDF);
      FOPDriver.format();
      FOPDriver.render();
    }
    catch (Exception e) {
      // Cannot write PDF output for the error anyway.
      // So maybe this stack trace will be useful info
      e.printStackTrace(System.err);
    }
  }
}

Note :  This release of Oracle XSQL Pages, release 9.0.2B, only works with Apache FOP release 0.19.0 or higher.

Deploy XSQL as Global Application to Oracle9iAS OC4J Servlet Container

You can now deploy the Oracle XSQL Pages framework to the Oracle9iAS OC4J Servlet Container as a global application. The installation steps are documented in the Installation section below.

Immediately Read Values Set as Cookies

Typically, cookies set as part of the current HTTP request are not "visible" to your application until the next time the browser makes a request. This is because new cookies are set as part of the HTTP response, while existing cookies are read from the HTTP request. To simplify the XSQL programming experience using cookies, you can now add the additional (optional) attribute immediate="yes" to the <xsql:set-cookie>to force the cookie value to be immediately "visible" as a parameter value to subsequent action elements in the current page. For upward compatibility, the default value for the immediate setting is false, if not specified.

Set Multiple Parameter Values with a Single SQL Statement

As a new alternative to their existing name attribute, the following action elements all support a new names attribute, allowing you to set multiple parameter values with a single SQL statement:

<xsql:set-page-param>

<xsql:set-session-param>

<xsql:set-stylesheet-param>

<xsql:set-cookie>

For example, you can now do:

<xsql:set-page-param names="fullname securitylevel last_visit"
                     bind-params="username password">
  select first_name||' '||last_name, 
         auth_level, 
         to_char(last_visit,'DD/MM/YYYY')
    from site_user_table
  where username = ?
    and password = ?
</xsql:set-page-param>

This will set the values of the fullname, securitylevel, and last_visit page-private parameters to the values of the first, second, and third columns respectively that are selected in the first row retrieved from the query. Any optional settings supported like only-if-unset and ignore-empty-value apply individually to each parameter being set.

Several Demos Updated to Use New Features

The ./svg/SalChart.xsql, ./doyouxml/doyouxml.xsql, ./emp/emp.xsql, and ./airport/airport.xsql pages were updated to use real bind variables instead of lexical replacement variables in their queries to improve database performance. The ./document/doc.xsql page was updated to use the new <xsql:include-xml>feature to select XML from a CLOB, eliminating the need for the clob.xsl stylesheet and the reparse="yes" attribute in the accompanying docstyle.xsql page.

Security Consideration for Production XSQL Pages Systems

Install Your XSQLConfig.xml File in a Safe Directory

The XSQLConfig.xml configuration file contains sensitive database username/password information that must be kept secure on the server. This file should not reside in any directory that is mapped to a virtual path of your Web server, nor in any subdirectory thereof. Its read permissions need only be granted such that the Unix account that owns the servlet engine can read it.

Failure to follow this recommendation could mean that a user of your site could accidentally (or intentionally) browse the contents of your configuration file.

Disable Default Client Stylesheet Overrideability When Your Pages Go Production

By default, the XSQL Page Processor allows the user to supply a stylesheet in the request by passing a value for the special xml-stylesheet parameter. If you want the stylesheet that is referenced inside if your server-side XSQL page to be the only stylesheet that is used, then you can include the allow-client-style="no" attribute on the document element of your page. You also can globally change the default setting to disallow client stylesheet overrides by changing a setting in your XSQLConfig.xml file. If you do this, then only pages that will allow client stylesheet overrides are ones that include the allow-client-style="yes" attribute on their document element.

Be Alert of the Use of Subsitution Parameters

With power comes responsibility. Any product, like Oracle Reports and XSQL Pages among others, that supports the use of lexical substitution variables in a SQL query can give a developer enough rope to hang himself. Any time you deploy an XSQL page that allows important parts of a SQL statement (or at the extreme, the entire SQL statement) to be substituted by a lexical parameter, you should make sure that you have taken appropriate precautions against misuse.

For example, one of the demonstrations that comes with XSQL Pages is the "adhoc query demo". It illustrates how the entire SQL statement of an <xsql:query>action handler can be supplied as a parameter. This is a powerful capability when in the right users hands, but be aware if you deploy a similar kind of page to your product system that the user can execute any query that the database security privileges for the connection associated with the page allows. The demo is setup to use a connection that maps to the SCOTT account, so a user of the "adhoc query demo" can query any data that SCOTT would be allowed to query from the SQL*Plus command line.

Techniques that can be used to make sure your pages are not abused include:

Making sure the database user account associated with the page has only the privileges for reading the tables/views you want your users to see.

Using true bind variables instead of lexical bind variables when substituting single values in a SELECT statement. If you need to make syntactic parts of your SQL statement parameterized, then lexical parameters are the only game in town. Otherwise, true bind variabled are recommended so that any attempt to pass an invalid value will generate an error instead of producing an unexpected result.

Reference Information

Online Help

http://yourmachine/xsql/index.html

XSQL Action Handler Summary

Parameter Resolution

HTTP Request Parameters

HTTP Cookies

HTTP Session Variables

Local XSQL Page Parameters

<xsql:query>
   select name from users where userid = {@myParam}
</xsql:query>

<xsql:query max-rows="{@myParam}">
   :
</xsql:query>

If the request is being processed by the XSQL Servlet, then check in the following order if myParam is the name of...

An XSQL local page parameter

An HTTP Cookie

An HTTP Session Variable

An HTTP Request Parameter

An XSQL local page parameter

An XSQL Request parameter

Provided on the command-line or passed into the XSQLRequest.process() method.

Known Issues

HTTP parameters with multibyte names (e.g. a parameter whose name is in Kanji) are properly handled when they are inserted into your XSQL page using <xsql:include-request-params>, but an attempt to refer to a parameter with a multibyte name inside the query statement of an <xsql:query>tag will return an empty string for the parameter's value. The workaround is to use a non-multibyte parameter name. The parameter can still properly have a multibyte value which will be handled correctly.

Using the CURSOR() function in SQL statements can cause an "Exhausted ResultSet" error if CURSOR() statements are nested and outer CURSOR() functions evaluate to an empty rowset.

When using the Apache JServ servlet engine, the use of relative stylesheet and XSQL page references may not produce the results you expect if you have an elaborate virtual path mapping. Since the Apache JServ engine does not properly implement the Servlet API method getRealPath() that the XSQL Servlet uses to resolve a path relative to the web server's virtual file system, a workaround is implemented for JServ to make relative path references work for the most common cases.

Installation

Supported Configurations

Supported Java JDK Versions

JDK 1.1.8

JDK 1.2.2

JDK 1.3

Note :  Numerous users have reported problems using XSQL Pages and the XSQL Servlet with JDK 1.1.7 which suffers problems in its character set conversion routines for UTF-8 that make it unusable for processing XSQL Pages.

Supported Servlet Engines

Oracle9iAS Apache/JServ Servlet Engine

Oracle9iAS OC4J Servlet Engine

Allaire JRun 2.3.3 and 3.0.0

Apache 1.3.9 with JServ 1.0 and 1.1

Apache 1.3.9 with Tomcat 3.1 or 3.2 Servlet Engine

Apache Tomcat 3.1 or 3.2 Web Server + Servlet Engine

Caucho Resin 1.1

Java Web Server 2.0

Weblogic 5.1 Web Server

NewAtlanta ServletExec 2.2 and 3.0 for IIS/PWS 4.0

Oracle8i Lite Web-to-Go Server

Oracle8i 8.1.7 Oracle Servlet Engine

Sun JavaServer Web Development Kit (JSWDK) 1.0.1 Web Server

Supported JSP Implementations

Oracle9iAS Apache/JServ Servlet Engine

Oracle9iAS OC4J Servlet Engine

Apache 1.3.9 with Tomcat 3.1 or 3.2 Servlet Engine

Apache Tomcat 3.1 or 3.2 Web Server + Tomcat 3.1 or 3.2 Servlet Engine

Caucho Resin 1.1 (Built-in JSP 1.0 Support)

NewAtlanta ServletExec 2.2 and 3.0 for IIS/PWS 4.0 (Built-in JSP 1.0 Support)

Oracle8i Lite Web-to-Go Server with Oracle JSP 1.0

Oracle8i 8.1.7 Oracle Servlet Engine

Any Servlet Engine with Servlet API 2.1+ and Oracle JSP 1.0

JDBC Drivers and Databases

Oracle8i 8.1.5 Driver for JDBC 1.x

Oracle8i 8.1.6 Driver for JDBC 1.x

Oracle8i 8.1.7 Driver for JDBC 1.x

Oracle8i Lite 4.0 Driver for JDBC 1.x

Oracle8i 8.1.6 Driver for JDBC 2.0

Oracle8i 8.1.7 Driver for JDBC 2.0

Oracle9i 9.0.1 Driver for JDBC 2.0

Prerequisites

Oracle XML Parser V2, version 9.0.1 or higher

Oracle XML SQL Utilities for Java, version 2.0.1 or higher

A Web Server that supports Java Servlets

A JDBC driver, like Oracle JDBC or Oracle8i Lite JDBC

XSQL Software Included in the XDK Distribution

Oracle XML Parser V2, .\lib\xmlparserv2.jar

Oracle XML SQL Utility, .\lib\xsu12.jar

Downloading and Installing the XSQL Servlet

Obtaining the XSQL Servlet Software from Oracle Technet

Visiting http://otn.oracle.com/tech/xml

Clicking on the 'Software' icon at the top of the page:

Logging in with your OTN username and password (registration is free if you do not already have an account).

Selecting whether you want the NT or Unix download (both contain the same files)

Acknowledging the licensing agreement and download survey

Clicking on appropriate *.tar.gz or *.zip file.

Extracting the Files in the Distribution

Choose a directory under which you would like the .\xdk directory and subdirectories to go. (e.g. C:\)

Change directory to C:\, then extract the XSQL downloaded archive file there. For example:

tar xvfz xdk_xxx.tar.gz

on Unix, or on Windows:

pkzip25 -extract -directories xdk_xxx.zip

using the pkzip25 command-line tool or the WinZip visual archive extraction tool.

Setting Up the Database Connection Definitions for Your Environment

<?xml version="1.0" ?>
<XSQLConfig>
     :
  <connectiondefs>
    <connection name="demo">
      <username>scott</username>
      <password>tiger</password>
      <dburl>jdbc:oracle:thin:@localhost:1521:ORCL</dburl>
      <driver>oracle.jdbc.driver.OracleDriver</driver>
    </connection>
    <connection name="lite">
      <username>system</username>
      <password>manager</password>
      <dburl>jdbc:Polite:POlite</dburl>
      <driver>oracle.lite.poljdbc.POLJDBCDriver</driver>
    </connection>
  </connectiondefs>
      :
</XSQLConfig>

Setting Up Your Servlet Engine to Run XSQL Pages

Include the list of XSQL Java archives:
xsu12.jar- Oracle XML SQL Utility

xmlparserv2.jar- Oracle XML Parser for Java V2

oraclexsql.jar- Oracle XSQL Pages

xsqlserializers.jar- Oracle XSQL Serializers for FOP/PDF Integration

classes12.jar- Oracle JDBC Driver

or the JAR file for the JDBC driver you will be using instead

as well as the directory where XSQLConfig.xml resides (by default ./xdk/admin) in the server CLASSPATH.

Note :  In a production system, make sure your XSQLConfig.xml file does not reside under a directory that is broweable from your web server. See Security Considerations for Production XSQL Pages System for more information.

Map the .xsql file extension to the oracle.xml.xsql.XSQLServlet servlet class

Map a virtual directory /xsql to the directory where you extracted the XSQL files (to access the on-line help and demos)

Oracle 9iAS 2.0 Oracle Containers for J2EE (OC4J) Servlet Container

You can deploy the xsqldemos.ear file to the OC4J server, or

You can install the XSQL Servlet as a global application to handle *.xsql files globally, similar to how *.jsp files are globally recognized and handled by the JSP-Runner servlet.

Change directory to the ./j2ee/home directory.

Deploy the ear file with the command (all on one line, replacing admin and welcome with your username and password for OC4J administration):

java -jar admin.jar ormi://yourserver/ admin welcome
  -deploy -file XDKHome/xdk/demo/java/xsql/xsqldemos.ear -deploymentName xsqldemos

Bind the web application with the command (all on one line, replace admin and welcome as above, using default-web-site or http-web-site as appropriate):

java -jar admin.jar ormi://yourserver/ admin welcome
  -bindWebApp xsqldemos xsqldemos http-web-site /xsql

http://yourserver:port/xsql/index.html

To install XSQL Servlet in the Oracle9iAS OC4J servlet container as a global application, do the following instead. Assuming your OC4J installation home is C:\j2ee\home, and that you've extracted the XDK distribution into the C:\xdk902 directory, here are the setup steps:

Verify that the following jar files are already in your C:\j2ee\home\lib directory (they should come preinstalled):
xmlparserv2.jar- Oracle XML Parser for Java V2

classes12.jar- Oracle JDBC Driver

Copy the following additional jar files from C:\xdk902\lib to C:\j2ee\home\lib...
xsu12.jar- Oracle XML SQL Utility

oraclexsql.jar- Oracle XSQL Pages

xsqlserializers.jar- Oracle XSQL Serializers for FOP/PDF Integration

Copy the C:\xdk\admin\XSQLConfig.xml configuration file to the C:\j2ee\home\default-web-app\WEB-INF\classes directory.

Edit the C:\j2ee\home\config\global-web-application.xml server configuration file to add a <servlet>and <servlet-mapping>entry as child elements of the <web-app>element as follows:

<orion-web-app ...etc... >
  :
 etc
  :
  <web-app>
    <servlet>
      <servlet-name>xsql</servlet-name>
      <servlet-class>oracle.xml.xsql.XSQLServlet</servlet-class>
    </servlet>
    <servlet-mapping>
      <servlet-name>xsql</servlet-name>
      <url-pattern>/*.xsql</url-pattern>
    </servlet-mapping>
     :
    etc
     :
  </web-app>
</web-app>

At this point, you can refer to any XSQL page in any virtual path and it will be processed by the XSQL Servlet. If you want to try the XSQL built-in samples, demos, and online help then you need to perform the following additional step to map a virtual path of /xsql/ to the C:\xdk\demo\java\xsql directory.

Edit the c:\j2ee\home\application-deployments\default\defaultWebApp\orion-web.xml file to add the following <virtual-directory>entry:

<orion-web-app ...etc...>
    :
   etc
    :
   <virtual-directory
   virtual-path="/technology/xsql"
   real-path="/technology/c:/xdk/xdk/demo/java/xsql/" />
    :
   etc
    :
</orion-web-app>

Note :  In a production system, make sure your XSQLConfig.xml file does not reside under a directory that is broweable from your web server. See Security Considerations for Production XSQL Pages System for more information.

Oracle Internet Application Server

Note :  In a production system, make sure your XSQLConfig.xml file does not reside under a directory that is broweable from your web server. See Security Considerations for Production XSQL Pages System for more information.

Allaire JRun 2.3.3

Setup the Server CLASSPATH Correctly for the XSQL Servlet

This is done by starting the JRun Administrator, clicking on the General tab, and clicking on the Java subtab as shown below.
 

Append the list of JAR files and directory that need to be in the server CLASSPATH for the XSQL Servlet to the existing value in the Java Classpath field. Assuming you installed into C:\, this list looks like:

To use the Oracle JDBC 1.x Driver, the list looks like:

C:\xsql\lib\xsu111.jar;
C:\xsql\lib\xmlparserv2.jar;
directory_where_JDBC_Driver_resides\classes111.zip;
C:\xsql\lib\oraclexsql.jar;
directory_where_XSQLConfig.xml_resides

To use Apache FOP for PDF Generation, you need to add:

C:\xsql\lib\xsqlserializers.jar;
FOPHOME/fop.jar;
FOPHOME/lib/batik.jar

To use the Oracle JDBC 2.0 Driver, the list looks like:

C:\xdk902\lib\xsu12.jar;
C:\xdk902\lib\xmlparserv2.jar;
directory_where_JDBC_Driver_resides\classes12.zip;
C:\xdk902\lib\oraclexsql.jar;
directory_where_XSQLConfig.xml_resides

To use Apache FOP for PDF Generation, you need to add:

C:\xdk902\lib\xsqlserializers.jar;
FOPHOME/fop.jar;
FOPHOME/lib/batik.jar

Map the .xsql file extension to the XSQL Servlet

To do this, select the Services tab in the JRun Administrator and select the appropriate "JRun Servlet Engine for XXX" entry for the Servlet Engine that corresponds to the web server that you are using. In the example below, we'll show configuring the Servlet Engine for the (built-in) JRun Web Server (JWS).
 

Then click the Service Config button...

On the Service Config screen, select the Mappings tab.
 

Click the Add button and make an entry for the *.xsql extension, indicating the name of the servlet to invoke of oracle.xml.xsql.XSQLServlet as shown above. Then click Save to save the changes, and Close to dismiss the dialog.

Map an /xsql/ virtual directory

In this step, we want to map the virtual path /xsql/ to C:\xdk902\xdk\demo\java\xsql\ (or wherever you installed the XSQL Servlet files).

If you are using JRun together with another web server like Apache, IIS, or others, the virtual directory mapping needs to be done using the web server configuration file/utility. If you are using the JRun Web Server, then you can configure this virtual path mapping from the JRun Adminstrator. To do this, select the "jws" service and click on Service Config.
 

Click on the Path Settings tab on the Service Config dialog, and click the Add button as show below.
 

Make an entry for a virtual path of /xsql/ (trailing slash important!) that maps to a Real Path of C:\xdk902\xdk\demo\java\xsql\ (trailing slash important!), or the appropriate directory into which you installed the XSQL Servlet files. Click Save to save the changes, then Close to dismiss the dialog.

http://localhost:8000/xsql/index.html

Apache JServ 1.0 or 1.1

Setup the Server CLASSPATH Correctly for the XSQL Servlet

This is done by editing the JServ configuration file named jserv.properties. Assuming you installed the XSQL Servlet files into C:\, you need to add the following entries:

To use the Oracle JDBC 1.x Driver, the entries look like:

# Oracle XML SQL Utility (XSU)
wrapper.classpath=C:\xdk902\lib\xsu111.jar
# Oracle XSQL Servlet
wrapper.classpath=C:\xdk902\lib\oraclexsql.jar
# Oracle JDBC (8.1.6) -- JDBC 1.x driver
wrapper.classpath=directory_where_JDBC_Driver_resides\classes111.zip
# Oracle XML Parser V2 (with XSLT Engine)
wrapper.classpath=C:\xdk902\lib\xmlparserv2.jar
# XSQLConfig.xml File location
wrapper.classpath=directory_where_XSQLConfig.xml_resides
# FOR Apache FOP Generation, Add
# wrapper.classpath=C:\xdk902\lib\xsqlserializers.jar
# wrapper.classpath=FOPHOME/fop.jar
# wrapper.classpath=FOPHOME/lib/batik.jar

To use the Oracle JDBC 2.0 Driver, the list looks like:

# Oracle XML SQL Utility (XSU)
wrapper.classpath=C:\xdk902\lib\xsu12.jar
# Oracle XSQL Servlet
wrapper.classpath=C:\xdk902\lib\oraclexsql.jar
# Oracle JDBC (8.1.6) -- JDBC 2.0 driver
wrapper.classpath=directory_where_JDBC_Driver_resides\classes12.zip
# Oracle XML Parser V2 (with XSLT Engine)
wrapper.classpath=C:\xdk902\lib\xmlparserv2.jar
# XSQLConfig.xml File location
wrapper.classpath=directory_where_XSQLConfig.xml_resides
# FOR Apache FOP Generation, Add
# wrapper.classpath=C:\xdk902\lib\xsqlserializers.jar
# wrapper.classpath=FOPHOME/fop.jar
# wrapper.classpath=FOPHOME/lib/w3c.jar

Map the .xsql file extension to the XSQL Servlet

To do this, you need to edit the JServ configuration file named jserv.conf (in JServ 1.0 this was named mod_jserv.conf on some platforms). Add the following line:

# Executes a servlet passing filename with proper extension in PATH_TRANSLATED 
# property of servlet request.
# Syntax: ApJServAction [extension] [servlet-uri]
# Defaults: NONE

ApJServAction .xsql /servlets/oracle.xml.xsql.XSQLServlet

Map an /xsql/ virtual directory

In this step, we want to map the virtual path /xsql/ to C:\xdk902\xdk\demo\java\xsql\ (or wherever you installed the XSQL Servlet files). To do this, you need to edit the Apache configuration file named httpd.conf and add the following line:

Alias /xsql/ "C:\xdk902\xdk\demo\java\xsql\"

http://localhost/xsql/index.html

Jakarta Tomcat 3.1 or 3.2

Setup the Server CLASSPATH Correctly for the XSQL Servlet

This is done by editing the Tomcat startup script named tomcat.bat in ./jakarta-tomcat/bin and adding five lines to append the appropriate entries onto the system CLASSPATH before the Tomcat server is started as shown below:

To use the Oracle JDBC 1.x Driver, the entries look like:

rem Set up the CLASSPATH that we need

set cp=%CLASSPATH%

set CLASSPATH=.
set CLASSPATH=%TOMCAT_HOME%\classes
set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\webserver.jar
set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\jasper.jar
set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\xml.jar
set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\servlet.jar
set CLASSPATH=%CLASSPATH%;%JAVA_HOME%\lib\tools.jar

REM Added for Oracle XSQL Servlet
REM -----------------------------
set CLASSPATH=%CLASSPATH%;C:\xdk902\lib\xsu111.jar
set CLASSPATH=%CLASSPATH%;C:\xdk902\lib\oraclexsql.jar
set CLASSPATH=%CLASSPATH%;C:\xdk902\lib\xmlparserv2.jar
set CLASSPATH=%CLASSPATH%;directory_where_JDBC_Driver_resides\classes111.zip
set CLASSPATH=%CLASSPATH%;directory_where_XSQLConfig.xml_resides
REM FOR Apache FOP Generation, Add
REM set CLASSPATH=%CLASSPATH%;C:\xdk902\lib\xsqlserializers.jar
REM set CLASSPATH=%CLASSPATH%;FOPHOME/fop.jar
REM set CLASSPATH=%CLASSPATH%;FOPHOME/lib/batik.jar

To use the Oracle JDBC 2.0 Driver, the list looks like:

rem Set up the CLASSPATH that we need

set cp=%CLASSPATH%

set CLASSPATH=.
set CLASSPATH=%TOMCAT_HOME%\classes
set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\webserver.jar
set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\jasper.jar
set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\xml.jar
set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\servlet.jar
set CLASSPATH=%CLASSPATH%;%JAVA_HOME%\lib\tools.jar

REM Added for Oracle XSQL Servlet
REM -----------------------------
set CLASSPATH=%CLASSPATH%;C:\xdk902\lib\xsu12.jar
set CLASSPATH=%CLASSPATH%;C:\xdk902\lib\oraclexsql.jar
set CLASSPATH=%CLASSPATH%;C:\xdk902\lib\xmlparserv2.jar
set CLASSPATH=%CLASSPATH%;directory_where_JDBC_Driver_resides\classes12.zip
set CLASSPATH=%CLASSPATH%;directory_where_XSQLConfig.xml_resides
REM FOR Apache FOP Generation, Add
REM set CLASSPATH=%CLASSPATH%;C:\xdk902\lib\xsqlserializers.jar
REM set CLASSPATH=%CLASSPATH%;FOPHOME/fop.jar
REM set CLASSPATH=%CLASSPATH%;FOPHOME/lib/batik.jar

Map the .xsql file extension to the XSQL Servlet

Tomcat supports creating any number of configuration "contexts" to better organize the web applications your site needs to support. Each context is mapped to a virtual directory path, and has its own separate servlet configuration information. XSQL Servlet comes with a pre-configured context file to make XSQL Servlet setup easier.

By default, Tomcat 3.1 and 3.2 come pre-configured with the following contexts (defined by <Context>entries in the ./jakarta-tomcat/conf/server.xml file)...

The root context

/examples

/test

/admin

We could install XSQL Servlet into one of these, but for simplicity we'll create a new context just for the XSQL Servlet that maps to the directory where you installed the XSQL Servlet distribution.

Edit the ./jakarta-tomcat/conf/server.xml file to add the following <Context>entry with a path="/technology/xsql"...

<Context path="/technology/test" docBase="webapps/test" debug="0" reloadable="true" /> 

<!--
 |  Define a Servlet context for the XSQL Servlet  
 |  
 |  The XSQL Servlet ships with a .\WEB-INF directory
 |  with its web.xml file pre-configured for C:\xdk902\xdk\demo\java\xsql
 |  installation.
 +-->
<Context path="/technology/xsql" docBase="C:\xdk902\xdk\demo\java\xsql"/>

Note that the docBase="C:/xsql" points to the physical directory where you installed the XSQL Servlet distribution. You then need to create a WEB-INF subdirectory directory in the C:\xdk902\xdk\demo\java\xsql and save into it the following ./WEB-INF/web.xml file:

<?xml version = '1.0' encoding = 'UTF-8'?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.2//EN"
                         "http://java.sun.com/j2ee/dtds/web-app_2_2.dtd">
<web-app>
   <servlet>
      <servlet-name>oracle-xsql-servlet</servlet-name>
      <servlet-class>oracle.xml.xsql.XSQLServlet</servlet-class>
   </servlet>
    <servlet-mapping>
      <servlet-name>oracle-xsql-servlet</servlet-name>
      <url-pattern> *.xsql </url-pattern>
   </servlet-mapping>
</web-app>

Note :  To add the XSQL Servlet to an existing context, add the servlet and servlet-mapping entries that you find in web.xml file above into the web.xml file for the context in question.

Map an /xsql/ virtual directory

This is already achieved by creating the /xsql context above.

http://localhost:8080/xsql/index.html

Note :  If you use Tomcat with an XML Parser (like the Sun Crimson Parser) that only supports DOM Leve 1 interfaces, then you must edit tomcat.bat to insure that the Oracle XML Parser's archive xmlparser.jar comes before the DOM Level 1 parser's archive in the classpath. For example, you could edit tomcat.bat to add the following line: REM NEED TO PUT xmlparserv2.jar FIRST before parser.jar set CP=C:\xdk902\lib\xmlparserv2.jar;%CP% just before the line: echo Using CLASSPATH: %CP% echo. set CLASSPATH=%CP%

ServletExec 2.2

Setup the Server CLASSPATH Correctly for the XSQL Servlet

This is done by browsing the url http://localhost/servlet/admin after starting the IIS Server, and clicking the VM Settings link under "Advanced" in the sidebar.
 

Add the four archives and one directory as shown above, by adding them one at a time and clicking the Submit button after each new entry.

Map the .xsql file extension to the XSQL Servlet

Click on Configure under the "Servlets" heading in the sidebar to browse the form where you register servlets. Enter a Servlet Name of oraclexsql and a Servlet Class of oracle.xml.xsql.XSQLServlet into the blank form at the top and click Submit. It should then look like the picture below:
 

Then, click on Aliases under "Servlets" in the sidebar.

Add an entry as shown below mapping *.xsql to the servlet "nickname" of oraclexsql you defined above.
 

Map an /xsql/ virtual directory

Use the IIS Admin console to create an /xsql virtual directory and map it to C:\xdk902\xdk\demo\java\xsql as shown here:
 

http://localhost/xsql/index.html

Setting Up the Demo Data

Change directory to the .\xsql\demo directory on your machine.

In this directory, run SQLPLUS. Connect to your database as CTXSYS/CTXSYS (the schema owner for Intermedia Text packages) and issue the command

GRANT EXECUTE ON CTX_DDL TO SCOTT;

Connect to your database as SYSTEM/MANAGER and issue the command:

GRANT QUERY REWRITE TO SCOTT;

This allows SCOTT to create a functional index that one of the demos uses to perform case-insensitive queries on descriptions of airports.

Connect to your database as SCOTT/TIGER.

Run the script install.sql in the ./xsql/demo directory. This script will, in turn, run all the SQL scripts for all the demos.

Change directory to the doyouxml subdirectory, and run the command imp scott/tiger file=doyouxml.dmp to import some sample data for the "Do You XML? Site" demo.

Note :  To properly experience the Scalable Vector Graphics demonstration, you need to install an SVG plugin into your browser like the Adobe SVG Plugin.

Additional Technical Tips

Producing XML from SQL with Nested Structure

Strongly Typed, User-Defined Object

Strongly Typed, User-Defined Collection

Untyped Collection based on a SQL statement

Using User-Defined Object Types

CREATE TYPE POINT AS OBJECT (X NUMBER, Y NUMBER);

CREATE TABLE LOCATION (
  NAME   VARCHAR2(80),
  ORIGIN POINT
);

INSERT INTO LOCATION VALUES ( 'Someplace', POINT(11,17) );
COMMIT;

<xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
  SELECT name, origin
    FROM location loc
   WHERE loc.origin.x = {@x-coord}
</xsql:query>

http://yourmachine.com/xsql/demo/point.xsql?x-coord=11

<ROWSET>
   <ROW num="1">
      <NAME>Someplace</NAME>
      <ORIGIN>
         <X>11</X>
         <Y>17</Y>
      </ORIGIN>
   </ROW>
</ROWSET>

Note :  See the supplied ./xsql/demo/point/point.sql script to create the example type and table above on your database. Also in the ./xsql/demo directory, see the ./empdept/empdeptobjs.sql, ./classerr/invalidclasses.sql, and ./insclaim/insclaim.sql files for additional object view examples.

Using the CURSOR Operator for Nested Rowsets

Taking the familar DEPT and EMP tables as an example, the following XSQL Page contains a query that selects the DNAME column from the DEPT table, and for each row returned a nested rowset of the EMPLOYEES from the EMP table who work in that department:

<xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
  SELECT dname,
         CURSOR( SELECT ename,sal
                   FROM emp
                  WHERE emp.deptno = dept.deptno) as employees /* Column Alias */
    FROM dept
   WHERE deptno = {@department}
</xsql:query>

http://yourserver.com/xsql/demo/empdept.xsql?department=10

<ROWSET>
   <ROW num="1">
      <DNAME>ACCOUNTING</DNAME>
      <EMPLOYEES>
         <EMPLOYEES_ROW num="1">
            <ENAME>CLARK</ENAME>
            <SAL>2450</SAL>
         </EMPLOYEES_ROW>
         <EMPLOYEES_ROW num="2">
            <ENAME>KING</ENAME>
            <SAL>5000</SAL>
         </EMPLOYEES_ROW>
         <EMPLOYEES_ROW num="3">
            <ENAME>MILLER</ENAME>
            <SAL>1300</SAL>
         </EMPLOYEES_ROW>
      </EMPLOYEES>
   </ROW>
</ROWSET>

Since the EMPLOYEES column is a nested rowset, it appears as a set of <ROW>elements nested within its parent <ROW>. Given the facts that:

One or more CURSOR operators can be used in the SELECT list of any SQL statement,

One or more CURSOR operators can be used in the SELECT list of SQL statements that appear inside any CURSOR operator (to any level of nesting),

The SQL statement that may appear within the CURSOR operator can be virtually any valid SQL statment (including GROUP BY and ORDER BY, etc), and that

Any SQL statement can be included in an <xsql:query>tag in an XSQL Page

Of course, by using these query techniques in the <xsql:query>tags of an XSQL Page, you can combine rich master/detail XML "datapages" with powerful database sorting and grouping applied to them by the SQL engine before subsequently applying an XSLT Transformation to the resulting datapage as we learned above to transform the resulting datapage into any "presentation" format you need.

Inserting XML Into Any Table You Require

Overview

The OracleXMLSave class understands how to insert any information which OracleXMLQuery knows how to produce. Said another way, the canonical structure of the output from OracleXMLQuery defines the kinds of structures for input which OracleXMLSave can automatically insert for us.

The fact that OracleXMLSave can only insert XML documents that look like XML documents produced by OracleXMLQuery may at first sound like a drastic limitation. However, this is not the case by any stretch of the imaginition. By taking advantage of an appropriate XSL transformation, virtually any XML document can be transformed to have the canonical format required by OracleXMLSave. This means that given:

An arbitrary XML document X, and

A database table T where you want to insert it

Let's say that the source document is an XML news feed like what you'll see if you browse the following URL from www.moreover.com ...

http://www.moreover.com/cgi-local/page?index_xml+xml

<?xml version="1.0"?>
   <!DOCTYPE moreovernews SYSTEM "http://www.moreover.com/xml/moreovernews.dtd">
   <moreovernews>
      <article id="4227581">
         <url>http://d.moreover.com/click/here.pl?x4227575</url>
         <headline_text>Austin: webMethods gets deal with Dell</headline_text>
         <source>dbusiness.com</source>
         <media_type>text</media_type>
         <cluster>XML and metadata news</cluster>
         <tagline>Austin</tagline>
         <document_url>http://washington.dbusiness.com/</document_url>
         <harvest_time>Oct 30 1999  7:08AM</harvest_time>
         <access_registration> </access_registration>
         <access_status> </access_status>
      </article>
      <article id="4212701">
         <url>http://d.moreover.com/click/here.pl?x4212698</url>
         <headline_text>Microsoft continues XML push with resource kit</headline_text>
         <source>InfoWorld</source>
         <media_type>text</media_type>
         <cluster>XML and metadata news</cluster>
         <tagline></tagline>
         <document_url>http://www.infoworld.com/</document_url>
         <harvest_time>Oct 29 1999  7:27AM</harvest_time>
         <access_registration> </access_registration>
         <access_status> </access_status>
      </article>
      <article id="4202251">
         <url>http://d.moreover.com/click/here.pl?x4202247</url>
         <headline_text>IBM Brings XML To MQSeries</headline_text>
         <source>Internet Week</source>
         <media_type>text</media_type>
         <cluster>XML and metadata news</cluster>
         <tagline></tagline>
         <document_url>http://www.internetwk.com/</document_url>
         <harvest_time>Oct 28 1999  4:28PM</harvest_time>
         <access_registration> </access_registration>
         <access_status> </access_status>
      </article>
      <article id="4082434">
         <url>http://d.moreover.com/click/here.pl?x4082432</url>
         <headline_text>XML leader OnDisplay's travel clients praised</headline_text>
         <source>Web Travel News</source>
         <media_type>text</media_type>
         <cluster>XML and metadata news</cluster>
         <tagline></tagline>
         <document_url>http://www.webtravelnews.com/</document_url>
         <harvest_time>Oct 20 1999  7:34AM</harvest_time>
         <access_registration> </access_registration>
         <access_status> </access_status>
      </article>
    </moreovernews>

CREATE TABLE newsstory(
 id     NUMBER PRIMARY KEY,
 title  VARCHAR2(200),
 url    VARCHAR2(200),
 source VARCHAR2(200)
);

So, we need to produce an XSL transformation that transforms the information in the XML news feed from Moreover.com into the canonical format that OracleXMLSave knows how to insert into the newsstory table.

We start by using an XSQL page to quickly produce one row of canonical query results from the newstory table. We can produce this with the XSQL Page:

<?xml version="1.0"?>
<query connection="demo" max-rows="1">

 SELECT *
   FROM NEWSTORY

</query>

xsql newstory.xsql newsstory.xml

<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <ID>1911</ID>
      <TITLE>Some Title</TITLE>
      <URL>http://somemachine/somepage.html</URL>
      <SOURCE>SomeSource</SOURCE>
   </ROW>
</ROWSET>

Add an xsl namespace declaration to the document element,

Remove the num="1" attribute from the <ROW>element,

Remove any elements corresponding to columns whose values will be assigned by database triggers, like the ID column will be in this example,

Surround the <ROW>element with an <xsl:for-each>that loops over the <article>elements in the source document from Moreover.com, and

Replace the literal text between the <TITLE>, <URL>, and <SOURCE>elements with an appropriate <xsl:value-of>element to plug-in the appropriate information from the current<article>we're looping over in the enclosing <xsl:for-each>loop.

SELECT * FROM NEWSSTORY

query produces the following XSL Transformation:

<?xml version = '1.0'?>
<ROWSET xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:for-each select="moreovernews/article">
   <ROW>
      <TITLE><xsl:value-of select="headline_text"/></TITLE>
      <URL><xsl:value-of select="url"/></URL>
      <SOURCE>Moreover.com</SOURCE>
   </ROW>
   </xsl:for-each>
</ROWSET>

Added the xsl namespace declaration to the <ROWSET>element:

<ROWSET xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

Added the <xsl:for-each>element to loop over each <article>element that is a child of the <moreovernews>element in the source document:

<xsl:for-each select="moreovernews/article">

so that for each <article>we find in the source, we create a <ROW>in the target.

Removed the <ID>element since its value will be assigned by our database trigger that assign new story id's from a database sequence.

Changed the:

<TITLE>Some Title</TITLE>
<URL>http://somemachine/somepage.html</URL>

tags to:

<TITLE><xsl:value-of select="headline_text"/></TITLE>
<URL><xsl:value-of select="url"/></URL>

to plug-in the value of the current <headline_text>and <url>for the current <article>.

Hard-coded the value of the <SOURCE>element to Moreover.com to indicate where this news feed came from.

java oracle.xml.parser.v2.oraxsl moreover.xml moreover-to-newsstory.xsl out.xml

Note :  Since the moreover.xml file includes the: <!DOCTYPE moreovernews            SYSTEM "http://www.moreover.com/xml/moreovernews.dtd"> declaration with an http:// reference to the MoreoverNews DTD that is outside of Oracle's firewall, you need to provide the following three extra Java VM parameters to allow the Oracle XML Parser to properly find and parse the DTD through our Proxy server: java -DproxySet=true       -DproxyHost=proxyServerName       -DproxyPort=80 oracle.xml.parser.v2.oraxsl ...

If we use Internet Explorer to browse the resulting out.xml file, we can see the results of the transformation:
 

If we pass this resulting target document to the OracleXMLSave class, as we'll do below, it will effortlessly insert all of the Moreover.com data into our newsstory table.

The last step before showing how to tie it all together using Java or XSQL Pages is to create the database trigger to automatically assign the value of the ID column on insert. Assuming we have a sequence named newsstory_id_seq lying around, the code looks like:

CREATE TRIGGER newsstory_autoid
BEFORE INSERT ON newsstory FOR EACH ROW
BEGIN
  SELECT newsstory_id_seq.nextval
    INTO :new.id
    FROM dual;
END;

Inserting Posted XML into the Database

Choose the table or view you want to use for inserting the XML information,

Create an XSL Transformation that transforms the inbound document into the canonical format for this table or view,

We saw a "cookbook" approach above for how to do this easily based on a SELECT * query against the target table or view,

Transform the inbound document into the canonical format for the table or view into which you want to insert it, and

Let OracleXMLSave insert the transformed document into your table or view.

<xsql:insert-request 
      table="tableorviewname"
      transform="transformname.xsl"/>

<?xml version="1.0?>
<xsql:insert-request xmlns:xsql="urn:oracle-xsql" 
      connection="demo" 
      table="newsstory"
      transform="moreover-to-newsstory.xsl"/>

Due to the nature of this news feed, news stories stay in the feed for a few days. If we want to avoid inserting the same story over and over again, we can easily do that by making sure we don't insert a story unless its Title and URL are a unique combination in our NEWSSTORY table.

Let's implement this behavior using a database INSTEAD OF INSERT trigger. Creating an INSTEAD OF INSERT trigger allows us to write code which will be executed in the database whenever an INSERT of any kind is performed. In the code of the INSTEAD OF INSERT trigger, we can check for uniqueness of the newsstory and only really insert it if it is unique.

Since INSTEAD OF triggers can only be defined on database views in Oracle8i, we simply need to create the newstoryview as follows:

CREATE VIEW newsstoryview AS
SELECT *
  FROM newsstory

CREATE OR REPLACE TRIGGER insteadOfIns_newsstoryview
INSTEAD OF INSERT ON newsstoryview FOR EACH ROW
DECLARE
  notThere BOOLEAN := TRUE;
  tmp      VARCHAR2(1);
  CURSOR chk IS SELECT 'x'
                  FROM newsstory
                 WHERE title = :new.title
                  AND url   = :new.url;
BEGIN
  OPEN chk;
  FETCH chk INTO tmp;
  notThere := chk%NOTFOUND;
  CLOSE chk;
  IF notThere THEN
    INSERT INTO newsstory(title,url,source)
         VALUES (:new.title,:new.url,:new.source);
  END IF;
END;

CREATE UNIQUE INDEX newsstory_unique_title_url on newsstory(title,url);

Finally, the only thing left to do is to change the xsql:insert-request action element above to use the NEWSSTORYVIEW instead of the NEWSTORY table by changing the line:

<?xml version="1.0?>
<xsql:insert-request xmlns:xsql="urn:oracle-xsql" 
      connection="demo" 
      table="newsstoryview"
      transform="moreover-to-newsstory.xsl"/>

Note :  Using the same INSTEAD OF INSERT trigger technique above, it would be quite straightforward to support automatically updating XML information in the database as well. While OracleXMLSave can only perform insert operations, the INSTEAD OF INSERT trigger on your view could check for existience of a primary key, and if it exists, actually perform an UPDATE instead of an INSERT.

The <xsql:insert-request>tag can be combined with <xsql:query>tags in your XSQL page to first insert any posted XML document (if there is any), then return some data from queries. For example:

<?xml version="1.0"?>
<page connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:insert-request table="newsstoryview" transform="moreover-to-newsstory.xsl"/>
  <lateststories>
    <xsql:query tag-case="lower" max-rows="5" rowset-element="" row-element="story" >
      select * 
        from newsstory
      order by id desc
    </xsql:query>
  </lateststories>
</page>

When XML is posted to a web server through HTTP, it's ContentType is text/xml by convention. Sometimes, it's convenient to accept posted information as a set of HTML <FORM>parameters. When an HTML form is posted, the server receives it with a ContentType of application/x-www-form-urlencoded. When the XSQL Servlet receives an HTTP request with method="POST" from such a form, it internally converts the form parameters into an XML document that looks like:

<request>
  <parameters>
    <firstparamname>firstparamvalue</firstparamname>
       :
    <lastparamname>lastparamvalue</lastparamname>
  </parameters>
  <session>
    <firstparamname>firstsessionparamvalue</firstparamname>
       :
    <lastparamname>lastsessionparamvalue</lastparamname>
  </session>
  <cookies>
    <firstcookie>firstcookievalue</firstcookiename>
       :
    <lastcookie>firstcookievalue</lastcookiename>
  </cookies>
</request>

Note :  If multiple parameters are posted with the same name, then they will automatically be "row-ified" to make subsequent processing easier. This means, for example, that a request which posts or includes the following parameters: id=101 name=Steve id=102 name=Sita operation=update Will create a "row-ified" set of parameters like: <request>   <parameters>     <row>       <id>101</id>       <name>Steve</name>     </row>     <row>       <id>102</id>       <name>Sita</name>     </row>     <operation>update</operation>   </parameters>        : </request>

Using an <xsql:insert-request>tag like:

<xsql:insert-request 
      table="newsstoryview" 
      transform="request-to-newsstoryview.xsl"/

<?xml version = '1.0'?>
<ROWSET xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xsl:version="1.0">
   <xsl:for-each select="request/parameters">
   <ROW>
      <TITLE><xsl:value-of select="title_field"/></TITLE>
      <URL><xsl:value-of select="url_field"/></URL>
      <SOURCE>User-Submitted</SOURCE>
   </ROW>
   </xsl:for-each>
</ROWSET>

<html>
<body>
  Insert a new news story...
  <form action="insertnewsform.xsql" method="post">
    <b>Title</b><input type="text" name="title_field" size="30"><br>
    <b>URL</b><input type="text" name="url_field" size="30"><br>
    <br>
    <input type="submit">
  </form>
<body>
</html>

Using the mechanism we learned in the previous section for associating XSL Stylesheets with XSQL Pages, we can include an <?xml-stylesheet?>processing instruction at the top of the insertnewsform.xsql that refers to the lateststories.xsl XSL Stylesheet below:

<html xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <head>
    <title>Latest Stories</title>
  </head>
  <body>
  <h2>Thanks for your Story!</h2>
  Here's a list of the latest stories we've received...
    <table border="0" cellspacing="0">
      <xsl:for-each select="page/lateststories/story">
        <tr>
         <td><a href="{url}"><xsl:value-of select="title"/></a></td>
        </tr>
      </xsl:for-each>
    </table>
  </body>
</html>

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="lateststories.xsl"?>
<page connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:insert-request table="newsstoryview" transform="request-to-newsstory.xsl"/>
  <lateststories>
    <xsql:query tag-case="lower" max-rows="5" rowset-element="" row-element="story" >
      select * 
        from newsstory
      order by id desc
    </xsql:query>
  </lateststories>
</page>

...then rather than seeing the raw XML datagram returned by the insertnewsform.xsql page, this will be transformed using the lateststories.xsl stylesheet so that it shows up in her browser like:
 

as an HTML page instead of as raw XML.

So in addition to being possible for developers by using Java programs that leverage the Oracle XML SQL Utility directly, we've seen that it's easy to insert XML-based information into Oracle database tables or views without programming using XSQL Pages and XSLT Transformations.

Note :  Here we've used simple examples with simple tables, however OracleXMLQuery and OracleXMLSave work well with any kind of richly structured object and with all supported datatypes. This means that, while not shown here, the same techniques described in this paper can be applied used to: Insert richly strutured information into an Object View with nested types and nested collections Insert "fragments" or "chunks" of XML elements and their content (from an entire document to any desired granularity of sub-structure) into a CLOB column or an object type with a CLOB attribute. The ./xsql/demo/empdeptobjs.sql script shows a working example of an a "department" object view ( based over the familiar EMP and DEPT tables) and an accompanying INSTEAD OF INSERT trigger on the object view to allow structured inserting of multiple departments and nested employees.

Demos Included with This Release

Closing Comments

Note :  Some of the material included in this document is excerpted from Building Oracle XML Applications by permission of O'Reilly and Associates.