Oracle XSQL Pages and the XSQL Servlet

Release Notes for Version 9.0.2.0.0D (Beta)

Contents

• Overview
• What are XSQL Pages?
• Release 9.0.2D
• Bugs Fixed
• Release 9.0.2C
• New Features
• Bugs Fixed
• Release 9.0.2B
• New Features
• Bugs Fixed
• Release 9.0.2A
• New Features
• Bugs Fixed
• Security Consideration for Production XSQL Pages Systems
• Reference Information
• Online Help
• XSQL Action Handler Summary
• Parameter Resolution
• Known Issues
• Installation
• Supported Configurations
• Prerequisites
• XSQL Software Included in the XDK Distribution
• Downloading and Installing the XSQL Servlet
• Additional Technical Tips
• Producing XML from SQL with Nested Structure
• Inserting XML Into Any Table You Require
• Demos Included with This Release
• Closing Comments

As the Internet drives an explosive demand for flexible information exchange, more and more application developers need to put their business data to work over the Web. Developers require standards-based solutions to this problem and SQL, XML, and XSLT are the standards that can get the job done in practice.

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.

Of course, Oracle 8i, the Oracle XML Developer's Kit, and the XML SQL Utility for Java provide all of the core technology needed by developers to implement this solution. However it is Oracle XSQL Pages that bring this capability to the "rest of us" by automating the use of these underlying XML technology components to solve the most common cases without programming.

Oracle XSQL Pages are templates that allow anyone familiar with SQL to declaratively:

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

The two key design goals of Oracle XSQL Pages are:

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

XSQL Pages are simple to build. Just use any text editor to create an XML file that includes <xsql:query> tags wherever you want to include XML-based SQL query results in the template. Associate an XSLT stylesheet to the page by including one extra line at the top of the file: an <?xml-stylesheet?> instruction. Save the file and request it through your browser to get immediate results. Since you can extend the set of actions that can be performed to assemble the "datapage" using the <xsql:action> element, it's possible to cleverly extend the basic simple model to handle harder jobs. Let's start by looking at a simple example of an XSQL Page.

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

you might write an XSQL Page like:

<?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>

To return the same information in HTML or some alternative XML format that might comply with a particular DTD you've been given, just associate an appropriate with <?xml-stylesheet?> like this:

<?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>

Once you've built a set of XSQL Pages, you can "exercise" your templates by:

• 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

These release notes explain in detail how to setup the Oracle XSQL Servlet and how to make use of all the time-saving features supported by XSQL Page templates.

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.2D fixes several bugs.

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

Release 9.0.2C adds several new features and fixes several bugs.

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

  1. <xsql:set-page-param>

  2. <xsql:set-session-param>

  3. <xsql:set-stylesheet-param>

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

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

Release 9.0.2B adds several new features and fixes several bugs.

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

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

Release 9.0.2A adds a few new features and fixes several bugs.

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

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

  2. 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="/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.

  
  

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

As with any software running on a server, care must be taken to avoid risks of exposing sensitive information to malicious users. This section describes best practice security techniques for using the Oracle XSQL Servlet.

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

This release includes an on-line Help System (built using XSQL Pages, of course) giving helpful information about the syntax and options for each XSQL Action Element. In additional helpful examples are provided illustrating each action. To access the help system, after successfully installing the XSQL Servlet, browse the URL:

http://yourmachine/xsql/index.html

A number of new built-in XSQL Action Elements have been added in this release. The following table documents the new arrivals...

XSQL provides a single way to refer to values that can be specified as:

• HTTP Request Parameters

• HTTP Cookies

• HTTP Session Variables

• Local XSQL Page Parameters

When you reference an parameter like myParam inside the content of an XSQL Action Element, like:

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

or in the attribute value of an XSQL Action Element, like:

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

the XSQL Page Processor determines the value of the parameter by using the following logic.

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

1. An XSQL local page parameter

2. An HTTP Cookie

3. An HTTP Session Variable

4. An HTTP Request Parameter

If the request is being processed by a non-Servlet request method using XSQLCommandLine or the XSQLRequest class, then check in the following order if myParam is the name of...

1. An XSQL local page parameter

2. An XSQL Request parameter

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

In either case, if none of the attempts produces a matching parameter value, the XSQL Page Processor looks for a "fallback" (a.k.a "default") value for myParam by searching the current Action Element and its ancestor elements in order to find an XML attribute of the same name as the parameter. If such an attribute is found, it's value is used as the value of myParam.

This release contains the following 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.

The XSQL Servlet is designed to run on any Java VM, using any JDBC driver, against any database. In practice, we are able to test it against only the most popular configurations of these. In this section we document the supported configurations that have been tested in the Oracle labs.

The XSQL Pages and XSQL Servlet have been tested using:

• JDK 1.1.8

• JDK 1.2.2

• JDK 1.3

These are the only three JDK versions that we know work correctly.

This XSQL Servlet has been tested with the following 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

JavaServer Pages can use <jsp:forward> and/or <jsp:include> to collaborate with XSQL Pages as part of an application. The following JSP platforms have been tested:

• 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

In general, it should work with any servlet engine supporting the Servlet 2.1 Specification or higher, and the Oracle JSP 1.0 reference implementation or functional equivalent from another vendor.

The Oracle XSQL Page processor has been designed to exploit the maximum set of features against the Oracle JDBC drivers, but gracefully degrade to work against any database with a reasonable JDBC driver. While numerous users have reported successfully using XSQL Pages with many other JDBC drivers, the ones that we have tested in-house are:

• 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

Oracle XSQL Pages 9.0.2.0.0D depends on:

• 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

For your convenience, all of these dependent libraries are included with the XSQL Servlet distribution when you download it.

In addition to the Oracle XSQL Servlet archive itself in .\lib\oraclexsql.jar, and the optional .\lib\xsqlserializers.jar archive for FOP/PDF integration, the Oracle Technet (OTN) distribution of the XDK includes the following Oracle XDK components on which Oracle XSQL Pages depends:

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

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

You can download the XSQL Servlet as part of the Oracle XDK for Java download by:

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

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

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

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

5. Acknowledging the licensing agreement and download survey

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

To extract the contents of the XDK distribution, do the following:

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

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

The demos are set up to use the SCOTT schema on a database on your local machine (i.e. the machine where the web server is running). If you are running a local database and have a SCOTT account whose password is TIGER, then you are all set. Otherwise, you need to edit the .\xdk\admin\XSQLConfig.xml file to correspond to your appropriate values for username, password, dburl, and driver values for the connection named "demo".

<?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>

Unix users and any user wanting to install the XSQL Servlet on other web servers should continue with the instructions below depending on the web server you're trying to use. In every case, there are 3 basic steps:

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

   
   

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

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

Oracle IAS release 1.0 and beyond comes pre-configured to run XSQL Servlet. By default it's Apache JServ servlet engine contains all of the wrapper.classpath entries in jserv.conf to include the necessary Java archives to run XSQL. The XSQLConfig.xml file lives in the ./xdk/admin subdirectory of the IAS installation home.

The easiest way to install XSQL Servlet in the Oracle9iAS OC4J servlet container is to install it as a global application. 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:

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

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

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

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

5. 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="/xsql"
      real-path="/c:/xdk/xdk/demo/java/xsql/" />
       :
      etc
       :
   </orion-web-app>
   

Then, you can browse the demos using the URL http://yoursever:yourport/xsql/index.html

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

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

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

Restart the JRun server and browse the URL:

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

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
     

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

3. 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\"

Restart the Apache server and browse the URL:

http://localhost/xsql/index.html

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

2. 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="/xsql"...

   <Context path="/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="/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.

   
   

3. Map an /xsql/ virtual directory

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

Restart the Tomcat server and browse the URL:

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

REM NEED TO PUT xmlparserv2.jar FIRST before parser.jar
set CP=C:\xdk902\lib\xmlparserv2.jar;%CP%

echo Using CLASSPATH: %CP%
echo.
set CLASSPATH=%CP%

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

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

3. 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:

Restart the IIS server and browse the URL:

http://localhost/xsql/index.html

To setup the data for the demos do the following:

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

2. 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;

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

4. Connect to your database as SCOTT/TIGER.

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

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

In addition to "flat" database query results, using the techniques described in this section you can easily produce XML from database query with nested structure by selecting information from structured columns in your XSQL page. Structured columns can be one of three types:

1. Strongly Typed, User-Defined Object

2. Strongly Typed, User-Defined Collection

3. Untyped Collection based on a SQL statement

Since the underlying Oracle XML SQL Utility for Java natively supports all of these combinations for producing richly structure XML from SQL statements that make use of these features, your Oracle XSQL Pages gain this capability for "free". We'll look at two simple examples...

If you have used the object/relational capabilities of Oracle8i to create a user-defined object type called POINT using the command:

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

and have used your new POINT type as the datatype of the ORIGIN column in your LOCATION table with the DDL statement:

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

and have inserted a row into this LOCATION table using an INSERT statement with the POINT() constructor like...

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

Then, an XSQL page like point.xsql below that does a query over the LOCATION table like...

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

...when requested using a URL like:

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

produces the output:

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

This demonstrates how the nested X and Y attributes in the POINT datatype structure of the ORIGIN column appear automatically as nested <X> and <Y> elements in the XML output. This is about the simplest possible example of using a user-defined type to get more richly structured XML output from your object/relational database. See the included Insurance Claim Demo for a much more detailed example combining both object types and object views. Take a look at the the associated SQL script in ./xsql/demo/insclaim/insclaim.sql to see the SQL syntax for creating the object types and object views used by the demo.

If you have not created object types that contain a pre-defined structure, you can still introduce nested structure into your SQL queries using the CURSOR operator allows you to select a nested rowset as a column in the SELECT list of a query. While almost any nested query is legal to include inside the CURSOR operator in the SELECT list, the most useful is a query that selects a nested set of detail rows for the current "master" row.

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>

Requesting this

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

produces the resulting XML "datapage":

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

Note that the second column in the SELECT statement is the expression using the CURSOR() operator to select the details. Since it is a column like any other, it can be aliased to the column name EMPLOYEES by using the AS COLUMNALIAS syntax as shown above.

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

you can quickly see how powerful, structured information can be created on the fly to get almost any structure you are looking for. This allows the processing speed of the database to be exploited for sorting and grouping instead of having to rely on slower techniques that would attempt these operations on flat data from within the XSLT stylesheet.

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.

Oracle provides all the ingredients necessary to accomplish this task. The approach you take depends on whether you need to accomplish the insert from within your own Java program, or whether you want a simple, declarative way of inserting the target document.

The Oracle XML SQL Utility for Java contains the OracleXMLQuery class that we used above to get SQL query results out of the database. It also contains a companion class called OracleXMLSave that performs the opposite job of putting XML information back into Oracle tables or views.

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

You can create an XSL Transformation that transforms the source document X into a target document X2 having precisely the structure needed for automatic insertion into table T.

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
      

A shortened version of such a resulting XML document looks like this:

<?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)
);

And further, let's suppose that you want to insert information from the XML news feed from Moreover.com into this table.

So, we need to produce an XSL transformation that transforms the information in the XML news feed from Moreover.com into th