Oracle XSQL Pages and the XSQL Servlet

Release Notes for Version 1.0.4.1 (Production)


January 12, 2001


Contents

        Overview
                What are XSQL Pages?
        Release 1.0.4.1
                Bugs Fixed
        Release 1.0.4.0
                New Features
        Release 1.0.3.0
                New Features
                Bugs Fixed
        Release 1.0.2.0
                New Features
                Bugs Fixed
        Release 1.0.1.0
                New Features
                Bugs Fixed
        Security Consideration for Production XSQL Pages Systems
        Reference Information
                Quickstart Using the Web-to-Go Server on Windows
                Online Help
                XSQL Action Handler Summary
                Parameter Resolution
                Known Issues
        Installation
                Supported Configurations
                Prerequisites
                Software Included in the XSQL Servlet Distribution
                Downloading and Installing the XSQL Servlet
        Using XSQL Pages
                Producing Dynamic XML Documents from SQL Queries
                Using XSLT Stylesheets to Transform Data Into Any Format
                Producing XML from SQL with Nested Structure
                Inserting XML Into Any Table You Require
                Using the XSQL Command-Line Processor
                Built-in Action Handler Reference
        Advanced Topics
                Modifying XSQL Configuration Settings
                Using the XSQL Page Processor Programmatically
                Exploiting the Available Diagnostics
                Writing a Custom XSQL Action Handler
                Using a Custom XSQL Action Handler in an XSQL Page
                Defining Custom XSQL Action Element for your Handler
        Demos Included with This Release
        Closing Comments

Note :

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



Overview

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:

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.


What are XSQL Pages?

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

The two key design goals of Oracle XSQL Pages are:

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" xmlns:xsql="urn:oracle-xsql">

    SELECT Carrier, FlightNumber, Origin, TO_CHAR(ExpectedTime,'HH24:MI') Due
      FROM FlightSchedule
     WHERE TRUNC(ArrivalTime) = TRUNC(SYSDATE)
       AND Destination = '{@City}'
  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" xmlns:xsql="urn:oracle-xsql">

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

</xsql:query>

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

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 1.0.4.1

Release 1.0.4.1 fixes the following two bugs.


Bugs Fixed


Release 1.0.4.0

Release 1.0.4.0 is the fourth, production maintenance release of the Oracle XSQL Pages technology. It contains enhancements requested by XSQL Pages users.


New Features


Release 1.0.3.0

Release 1.0.3.0 is the third, production maintenance release of the Oracle XSQL Pages technology. It contains several improvements requested by XSQL Pages users, as well as several bug fixes.


New Features


Bugs Fixed


Release 1.0.2.0

Release 1.0.2.0 is the second, production maintenance release of the Oracle XSQL Pages technology. It contains several improvements requested by XSQL Pages users, as well as several bug fixes.


New Features


Bugs Fixed


Release 1.0.1.0

Release 1.0.1.0 was the first maintenance release of the Oracle XSQL Pages technology. It added several improvements requested by XSQL Pages users, as well as several bug fixes.


New Features


Bugs Fixed

The following reported bugs were fixed in this release:


Security Consideration for Production XSQL Pages Systems

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.


Reference Information


Quickstart Using the Web-to-Go Server on Windows

The XSQL Pages distribution includes a working version of the single-user web server that is part of the Web-to-Go mobile application platform along with the xsql-wtg.bat script to start the server with all XSQL Servlet settings setup properly.


Online Help

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


XSQL Action Handler Summary

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

Action Element Description
<xsql:set-stylesheet-param>

Set the value of a top-level XSLT stylesheet parameter.

<xsql:set-page-param>

Set a page-level (local) parameter that can be referred to in subsequent SQL statements in the page.

<xsql:set-session-param>

Set an HTTP-Session level parameter.

<xsql:set-cookie>

Set an HTTP Cookie.

<xsql:query>

Execute an arbitrary SQL statement and include its result set in canonical XML format.

<xsql:ref-cursor-function>

Includes the canonical XML representation of the result set of a cursor returned by a PL/SQL stored function.

<xsql:include-param>

Include a parameter and its value as an element in your XSQL page.

<xsql:include-request-params>

Include all request parameters as XML elements in your XSQL page.

<xsql:include-xml>

Include arbitrary XML resources at any point in your page by relative or absolute URL.

<xsql:include-owa>

Include the results of executing a stored procedure that makes use of the Oracle Web Agent (OWA) packages inside the database to generate XML.

<xsql:include-xsql>

Include the results of one XSQL page at any point inside another.

<xsql:insert-request>

Insert the XML document (or HTML form) posted in the request into a database table or view.

<xsql:update-request>

Update an existing row in the database based on the posted XML document supplied in the request.

<xsql:delete-request>

Delete an existing row in the database based on the posted XML document supplied in the request.

<xsql:insert-param>

Inserts the XML document contained in the value of a single parameter.

<xsql:dml>

Execute a SQL DML statement or PL/SQL anonymous block.

<xsql:action>

Invoke a user-defined action handler, implemented in Java, for executing custom logic and including custom XML information into your XSQL page.


Parameter Resolution

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

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.


Known Issues

This release contains the following known issues:


Installation


Supported Configurations

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.


Supported Java JDK Versions

The XSQL Pages and XSQL Servlet have been tested using:

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


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

This XSQL Servlet has been tested with the following servlet engines:


Supported JSP Implementations

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:

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.


JDBC Drivers and Databases

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:


Note :

If you are using the Oracle 8i 8.1.6 Driver for JDBC 2.0 (in classes12.zip) you must download and use the corresponding Oracle XML SQL Utility for JDBC 2.0 (in XSU12.zip) from Oracle Technology Network.



Note :

The XSQL Servlet distribution comes with the files necessary to use the Oracle 8.1.7 JDBC driver in "Thin" Java mode for single-byte languages. If you want to use:

  • Multi-byte character sets, or

  • JDBC OCI8 driver

Please download the entire Oracle 8.1.7 JDBC distribution from Technet.



Prerequisites

Oracle XSQL Pages 1.0.4.1 depends on:

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


Software Included in the XSQL Servlet Distribution

In addition to the Oracle XSQL Servlet archive itself in .\xsql\lib\oraclexsql.jar, the Oracle Technet (OTN) distribution of Oracle XSQL Pages 1.0.4.1 includes the following bundled releases of production-level Oracle XDK components on which Oracle XSQL Pages depends:

Beta releases of the following Oracle XDK components with which XSQL Servlet is also compatible can be downloaded separately from OTN:


Note :

If you download either or both of these beta releases, the installation instructions below make the assumption that you have copied their respective xmlparserv2.jar and xsu12.jar files into a directory named C:\xsql\lib\beta.



Downloading and Installing the XSQL Servlet


Obtaining the XSQL Servlet Software from Oracle Technet

You can download the XSQL Servlet distribution by:

  1. Visiting http://technet.oracle.com/tech/xml/xsql_servlet

  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 xsqlservlet_v1_0_4_1.tar.gz or xsqlservlet_v1_0_4_1.zip


Extracting the Files in the Distribution

To extract the contents of the XSQL Servlet distribution, do the following:

  1. Choose a directory under which you would like the .\xsql 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 xsqlservlet_v1_0_4_1.tar.gz

    on Unix, or on Windows:

    pkzip25 -extract -directories xsqlservlet_v1_0_4_1.zip

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


(Windows Only) Starting the Web-to-go Server

The XSQL Servlet comes bundled with the Oracle Web-to-go server that is pre-configured to use XSQL Pages. The Web-to-go web server is a single-user server, supporting the Servlet 2.1 API, used for mobile application deployment and for development. This is a great way to try XSQL Pages out on your Windows machine before delving into the details of configuring another Servlet Engine to run XSQL Pages.


Note :

The Web-to-go Web server is part of Oracle's development and deployment platform for mobile applications.For more information on Web-to-go, please visit http://www.oracle.com/mobile


Windows users can get started quickly with XSQL Pages by:

  1. Running the xsql-wtg.bat script in the .\xsql directory.

  2. Browsing the URL http://localhost:7070/xsql/index.html

If you get an error starting this script, edit the xsql-wtg.bat file to properly set the two environment variables JAVA and XSQL_HOME to appropriate values for your machine.

REM ----------------------------------------------
REM Set the 'JAVA' variable equal to the full path
REM of your Java executable.
REM ----------------------------------------------

set JAVA=J:\java1.2\jre\bin\java.exe

set XSQL_HOME=C:\xsql

REM ----------------------------------------------
REM Set the 'XSQL_HOME' variable equal to the full
REM path of where you install the XSQL Servlet 
REM distribution.
REM ----------------------------------------------

If you install XSQL into a directory other than C:\xsql (for example, on another drive like D:\xsql), you will also need to edit the ./xsql/wtg/lib/webtogo.ora file to change the value of the ROOT_DIR parameter as follows. Change...

[FILESYSTEM]
TYPE=OS
ROOT_DIR=C:\

to:

[FILESYSTEM]
TYPE=OS
ROOT_DIR=D:\

Then, repeat the two steps above.

If you get an error connecting to the database when you try the demos, you'll need to go on to the next section, then try the steps above again after setting up your database connection information correctly in the XSQLConfig.xml file.


Setting Up the Database Connection Definitions for Your Environment

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 .\xsql\lib\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>


Setting Up Your Servlet Engine to Run XSQL Pages

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:

    as well as the directory where XSQLConfig.xml resides (by default ./xsql/lib) 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.



    Note :

    For convenience, the xsqlservlet_v1_0_4_1.tar.gz and xsqlservlet_v1_0_4_1.zip distributions include the .jar files for the Oracle XML Parser for Java (V2), the Oracle XML SQL Utilities for Java, and the 8.1.6 JDBC driver in the .\lib subdirectory, along with Oracle XSQL Pages' own .jar archive.


  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 Internet Application Server

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/lib subdirectory of the IAS installation home.


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
  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 Apache FOP for PDF Generation, you need to add:

    C:\xsql\lib\xsqlserializers.jar;
    FOPHOME/fop_bin_0_14_0.jar;
    FOPHOME/lib/w3c.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:\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:\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

Apache JServ 1.0 or 1.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:

  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:\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:/xsql/"

Restart the Apache server and browse the URL:

http://localhost/xsql/index.html

Jakarta Tomcat 3.1 or 3.2
  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:

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

    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:\xsql
     |  installation.
     +-->
    <Context path="/xsql" docBase="C:\xsql"/>
    

    Note that the docBase="C:/xsql" points to the physical directory where you installed the XSQL Servlet distribution. Since the XSQL Servlet distribution already ships with a ./xsql/WEB-INF directory containing the required web.xml file with the <servlet> and <servlet-mapping> entries to enable the XSQL Servlet class to be mapped to the .xsql extension, this is the only step needed.


    Note :

    To add the XSQL Servlet to an existing context, add the servlet and servlet-mapping entries that you find in ./xsql/WEB-INF/web.xml 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

ServletExec 2.2
  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:\xsql as shown here:

Restart the IIS server and browse the URL:

http://localhost/xsql/index.html

Oracle Servlet Engine in Oracle8i Release 3 (v8.1.7)

Oracle 8i release 3 (version 8.1.7) ships with the Oracle Servlet Engine, a Servlet 2.2-compliant servlet engine that runs on the database Java VM. You can install the XSQL Servlet on the OSE servlet engine and dispatch requests to it using an Apache web server (via mod_ose) or using the HTTP server that runs inside the database and uses an HTTP listener running inside the database.

The following instructions assume you are starting with a brand new Oracle8i release 3 installation and have not yet defined any web service, endpoint, or web domain. If you already have a web domain created into which you want to publish the XSQL Servlet, you can ....


Setting Up the Demo Data

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.


Note :

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



Using XSQL Pages


Producing Dynamic XML Documents from SQL Queries

To use Oracle XSQL Pages just include a <xsql:query> tag in your XML file at the place where you want the SQL to be executed. The <xsql:query> element will be replaced by the XML output of your query.

The XSQL Page Processor expects to find an attribute named connection on your XML document's document element whose value must match the name of a connection defined in your XSQLConfig.xml file.

The simplest usage of the <xsql:query> tag is just:

<?xml version="1.0"?>
<xsql:query xmlns:xsql="urn:oracle-xsql" connection="demo">
  SELECT 'Hello World' AS "GREETING" FROM DUAL
</xsql:query>
which produces the resulting dynamically created XML document:
<?xml  version = '1.0'?>
<ROWSET>
   <ROW id="1">
      <GREETING>Hello World</GREETING>
   </ROW>
</ROWSET>
An .xsql page can have any number of <xsql:query> tags and they can appear anywhere you want them to in the page, even nested among other XML tags, so long as the .xsql parses as a well-formed XML document.
Note :

To have the dynamic XML-results of your <xsql:query> tags processed, you must:

  • Include a connection="connname" attribute on the document element of your page, and

  • Ensure that the value supplied for the connection name matches one of the entries in the XSQLConfig.xml configuration file which resides in the xsql/lib directory.

If your .xsql page does not include a connection="connname" attribute on the document element, the XML file will still be served to the requestor, however none of the <xsql:query> tags (if any) will be processed.


For example, you could build up a "data page" out of two queries like this:

<?xml version="1.0"?>
<sales-by-year xmlns:xsql="urn:oracle-xsql" connection="salesdb">
  <period id="H1" year="CY99">
    <xsql:query>
      SELECT salesperson, SUM(sales) AS Total
        FROM sales
       WHERE sale_date between '01-JAN-99' and '30-JUN-99'
     GROUP BY salesperson
    </xsql:query>
  </period>
  <period id="H2" year="CY99">
    <xsql:query>
      SELECT salesperson, SUM(sales) AS Total
        FROM sales
       WHERE sale_date between '01-JUL-99' and '31-DEC-99'
     GROUP BY salesperson
    </xsql:query>
  </period>
</sales-by-year>
which would produce results like:
<?xml version="1.0"?>
<sales-by-year connection="salesdb">
  <period id="H1" year="CY99">
    <ROWSET>
      <ROW id="1">
        <SALESPERSON>Steve</SALESPERSON>
        <TOTAL>23465500</TOTAL>
      </ROW>
      <ROW id="2">
        <SALESPERSON>Mark</SALESPERSON>
        <TOTAL>39983400</TOTAL>
      </ROW>
    </ROWSET>
  </period>
  <period id="H2" year="CY99">
    <ROWSET>
      <ROW id="1">
        <SALESPERSON>Steve</SALESPERSON>
        <TOTAL>67788400</TOTAL>
      </ROW>
      <ROW id="2">
        <SALESPERSON>Mark</SALESPERSON>
        <TOTAL>55786990</TOTAL>
      </ROW>
    </ROWSET>
  </period>
</sales-by-year>

While the two examples above show the default behavior for SQL-to-XML query results, you can customize many of the aspects of the XML query results produced for each <xsql:query> in your .xsql page by supplying one or more optional attributes on the appropriate <xsql:query> tag whose XML-results you'd like to affect.

The following attribute-based options correspond one-to-one to features offered by the underlying oracle.xml.sql.query. OracleXMLQuery class provided in the Oracle XML SQL Utilities for Java, which the XSQL Page Processor uses under the covers.

The XSQL Page Processor supports the following (case-sensitive) attributes on the <xsql:query> element:

<xsql:query> Tag Attributes to Control XML Output
Attribute Name Description
rowset-element

Element name to use for the query results. Set equal to the empty string to suppress printing a document element. (Default is <ROWSET>)

row-element

Element name to use for each row in the query results. Set equal to the empty string to supress printing a row element. (Default is <ROW>)

max-rows

Maximum number of rows to fetch from the query. Useful for fetching the "top-N" or, in combination with skip-rows, the "next-N" rows from a query result. (Default is to fetch all rows.)

skip-rows

Number of rows to skip over before returning the query results. (Default is not skip any rows)

id-attribute

Attribute name for the id attribute for each row in the query result. (Default is id)

id-attribute-column

Column name to use to supply the value of the id attribute for each row in the query result. (Default is to use the row count as the id attribute value)

null-indicator

If set to y or yes, causes a null-indicator attribute to be used on the element for any column whose value is NULL. (Default is to omit the element in the result for any column with a NULL value)

tag-case

If set to upper, causes the element names for columns in the query result to be in UPPERCASE. If set to lower, causes the element names for columns in the query result to be in lowercase. (Default is use the case of the column name (or column aliases if provided) from the query)

bind-params Ordered, space-separated list of XSQL parameter names to be bound to corresponding bind variables in the SQL statement.

The SQL statement provided in the content of the <xsql:query> element, as well as the values of XML attributes of the <xsql:query> element, may refer to lexical-substitution parameters using the syntax {@paramname}. The XSQL Page Processor will first consult the HTTP request parameters (or command-line parameters) for a matching parameter named paramname and if found, it will use that value. If not found among the HTTP request parameters, it will search for XML attributes on the current <xsql:query> element, and its ancestor elements until reaching the document root. Once a value for the parameter has been found, it is lexically string-substituted into the SQL statement before executing the query. So, for example, you could do:

<?xml version="1.0"?>
<xsql:query xmlns:xsql="urn:oracle-xsql" connection="xmldemo" 
       airport        = "SFO" 
       rowset-element = "Ok" 
       max-rows       = "1" 
       row-element    = "Airport" >

       SELECT tla "Code", description "Description"
	 FROM AIRPORT
	WHERE tla = UPPER('{@airport}')

</xsql:query>

to allow the HTTP request to supply the value of the airport parameter, but to use the value of "SFO" as the default value of the parameter if none is provided is the HTTP request:

http://yourserver/xsql/demo/airport.xsql?airport=SQL
The example above would return the following XML document (Note the use of custom rowset-element, row-element, and max-rows attributes):
<?xml  version = '1.0'?>
<Ok>
   <Airport id="1">
      <programlisting>SQL</programlisting>
      <Description>San Carlos, California, Usa</Description>
   </Airport>
</Ok>

Nested inside a <xsql:query> element you may supply an optional <xsql:no-rows-query> element. The syntax and options of the xsql:no-rows-query element follow that of the query element exactly. If the outer query produces no rows, the XSQL Page Processor will proceed to execute the query in the <xsql:no-rows-query> element. The no-rows-query elements can be nested to any level.

This allows you to augment the example above to do:

<?xml version="1.0"?>
<xsql:query xmlns:xsql="urn:oracle-xsql"
     connection       = "xmldemo" 
     airport          = "SFO" 
     rowset-element   = "Ok" 
     max-rows         = "1" 
     row-element      = "Airport" >

       SELECT tla "Code", description "Description"
	 FROM AIRPORT
	WHERE tla = UPPER('{@airport}')

  <xsql:no-rows-query 
     max-rows       = "10" 
     rowset-element = "Error" 
     row-element    = "Airport" >

       SELECT tla "Code", description "Description"
	 FROM AIRPORT
	WHERE UPPER(description) LIKE UPPER('%{@airport}%')
        ORDER BY tla
  </xsql:no-rows-query>

</xsql:query>

This example will initially attempt an exact match on an airport using its three-letter code, and if that produces no matching rows, the error query will attempt to do a LIKE match on the description. While the exact match query specifies it wants to return a maximum of one row, the error query returns up to the first 10 "fuzzy" matches on the candidate airport name passed in in the airport parameter. Also note the fact that the outer query requests a rowset-element of <Ok> while the no-rows query signals the failure to find an exact match by indicating that an <Error> tag be used for the rowset-element.


Using XSLT Stylesheets to Transform Data Into Any Format

Here we explore how to use the powerful concept of an XSL Transformation to "morph" the XML document containing our SQL query results into HTML, XML of another format, or simply text.


Overview

The W3C-standard XSL Transformation Language (XSLT) gives us the power to describe arbitrary transformations of XML into XML, HTML, or Text. An XSLT transformation describes how a source document should be transformed into a target document. It works in concert with the W3C-standard XML Path Language (XPath) that offers a URL-like syntax for referring to any information in the source document that is required in the target document, as well as providing a basic set of string, number, and boolean functions to operate on that information as part of the transformation.

In its simplest form, an XSLT transformation is just template that:

Given the XML output of an employee.xsql page:

<?xml version="1.0"?>
<query connection="demo" id="7839">

       SELECT *
         FROM EMP
       WHERE EMPNO = {@id}

</query>

you can use an XSL Transformation like:

<html xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <head>
    <title>Employee Info</title>
  </head>
  <body>
    <table border="1" cellspacing="0">
        <tr>
         <th>Employee Name</th>
         <th>Salary</th>
        </tr>
      <xsl:for-each select="ROWSET/ROW">
        <tr>
         <td><xsl:value-of select="ENAME"/></td>
         <td><xsl:value-of select="SAL"/></td>
        </tr>
      </xsl:for-each>
    </table>
  </body>
</html>

to produce HTML output like:

or an XSL Transformation like:

<hr:people-group xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                 xmlns:hr="http://www.oracle.com/human-resources">
  <hr:group-members><xsl:value-of select="count(ROWSET/ROW)"/></hr:group-members>
  <hr:people>
    <xsl:for-each select="ROWSET/ROW">
      <hr:person>
       	<hr:name><xsl:value-of select="ENAME"/></hr:name>
       	<hr:wages><xsl:value-of select="SAL"/></hr:wages>
      </hr:person>
    </xsl:for-each>
  </hr:people>
</hr:people-group>

to produce XML output complying with a fictitious Human Resources schema, requiring the XML to be in a particular format like:

In both the HTML and XML examples the template for the target document can literally represent the transformation, provided that you declare the xsl namespace using:

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

on the document element of the target page before making use of the special <xsl:for-each> and <xsl:value-of> tags to loop over and refer to data from the source document, respectively.

In both transformation examples above, notice that the value of the select attribute of <xsl:for-each> element is an XPath expression that selects the elements over which you want to loop. The value of the select attribute of the <xsl:value-of> elements is an XPath expression that selects the element whose value you want to insert at that spot in the target document.

Your intuition about filesystem "paths" and web URL "paths" should serve you well when using XPath expressions. If you think of the elements in the source document like directory names, then you'll quickly find an expression like ROWSET/ROW/ENAME very familiar looking.

Just like directories in a filesystem an XPath that starts with a slash is an absolute path from the "root" of the source document while an XPath that does not start with a slash is relative to the current context. This explains why an expression like:

<xsl:value-of select="ENAME">

can be used nested inside of an:

<xsl:for-each select="ROWSET/ROW">

element to refer relatively to the <ENAME> element inside each <ROW> that occurs as a child of the <ROWSET> element.

Now let's learn how to put an XSL Transformation into action in our programs.


Associating an XSLT Transformation/Stylesheet with Your XSQL Page

As we learned above, rather than returning the raw "datapage" document produced by processing your XSQL Page, you can use an XSL Transformation to transform those results into XML, HTML, or text by adding one line to the top of your XSQL page:

<?xml-stylesheet type="text/xsl" href="transformname.xsl"?>

This is the W3C Standard mechanism of Associating Style Sheets with XML Documents.

The result of adding a line like this to the top of our page from above:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="emphtml.xsl"?>
<query connection="demo" id="7839">

       SELECT *
         FROM EMP
       WHERE EMPNO = {@id}

</query>

causes the "raw" datapage to be transformed by the emphtml.xsl stylesheet before returning to the browser, so a request now for the URL:

http://yourmachine/xmlinout/emp.xsql?id=7499

produces the HTML page:

<html>
   <head>
      <META http-equiv="Content-Type" content="text/html; charset=UTF-8">
      <title>Employee Info</title>
   </head>
   <body>
      <table border="1" cellspacing="0">
         <tr>
            <th>Employee Name</th>
            <th>Salary</th>
         </tr>
         <tr>
            <td>ALLEN</td>
            <td>1600</td>
         </tr>
      </table>
   </body>
</html>

So by writing an XSQL page with some queries and associatng an XSL Transform to it, you can publish any data in your database into any desired XML, HTML, or Textual format that you need over the web without writing code. See the demos that accompany the XSQL Servlet release for lots of examples of combining SQL, XML, and XSLT.


Further Control on How XSLT Stylesheets are Applied

As we've seen, if you include an <?xml-stylesheet?> processing instruction at the top of your .xsql file, it will be considered by the XSQL Page Processor for use in transforming the resulting dynamically-produced "datapage" into either HTML or XML. For example:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="emp.xsl"?>
<xsql:query xmlns:xsql="urn:oracle-xsql"
       connection     = "demo"
       rowset-element = "employee-list" 
       row-element    = "employee"
       tag-case       = "lower" >

       SELECT *
         FROM EMP
      ORDER BY SAL DESC

</xsql:query>

would use the emp.xsl stylesheet to transform the results of the EMP query in the server tier, before returning the response to the requestor. The stylesheet is accessed by the relative or absolute URL provided in the href pseudo-attribute on the xml-stylesheet processing instruction.

Multiple <?xml-stylesheet?> processing instructions can be included at the top of the file and an optional media pseudo-attribute can be specified which will cause the XSQL Page Processor to try to select a stylesheet for transformation which is appropriate to the current requesting user agent. The XSQL Page Processor does a case-insensitive string match of the value provided on the media attribute with the value of the User-Agent from the HTTP request. The first matching processing instruction will be used. A processing instruction without a media matches all user agents so it can be used as the fallback/default.

For example, the following processing instructions at the top of an .xsql file...

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" media="lynx" href="doyouxml-lynx.xsl" ?>
<?xml-stylesheet type="text/xsl" media="msie" href="doyouxml-ie.xsl" ?>
<?xml-stylesheet type="text/xsl" href="doyouxml.xsl" ?>
<datapage xmlns:xsql="urn:oracle-xsql" connection="demo">
  :
  :

will use doyouxml-lynx.xsl for Lynx browsers, doyouxml-ie.xsl for Internet Explorer browsers, and doyouxml.xsl for all others.

One additional pseudo-attributes can be supplied on the <?xml-stylesheet?> to further control how the XSQL Page Processor handles XSLT transformations, the client attribute. If set to yes, then the page processor adds this processing instruction into the resulting "datapage" and lets the client do the XSLT transformation. Below is a summary of all the pseudo-attributes that can be specified on the xml-stylesheet processing instruction:

<?xml-stylesheet?> Pseudo-Attributes
Attribute Name Description
type

Must be present and be set to the value text/xsl, otherwise the <?xml-stylesheet?> instruction is ignored by the XSQL Page Processor.

href

Must be present. It indicates the relative or absolute URL to the stylesheet to be used.

media

This value is optional. If set, this attribute's value is used to perform a case-insensitive match on the User-Agent string of the requesting browser so that an appropriate stylesheet can be used depending on the requesting software/device.

client

If set to "yes" will download the raw XML to the client and include this <?xml-stylesheet?> processing instruction for processing in the browser. The default if not specified is to perform the transform in the server.

so a modified example using these extra attributes would look like:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" media="lynx" href="doyouxml-lynx.xsl" ?>
<?xml-stylesheet type="text/xsl" media="msie 5.0" client="yes" href="doyouxml-ie5.xsl" ?>
<?xml-stylesheet type="text/xsl" href="doyouxml.xsl" ?>
<datapage connection="xmldemo">

which will have the following behavior:

If the XSQL page has not explicitly forbidden it, you may also supply the XSL stylesheet URL as part of the HTTP request by specifying an extra URL parameter named xml-stylesheet. For example, requesting the URL:

http://yourserver/yourdatapage.xsql?param1=value&xml-stylesheet=yourstyle.xsl

will cause the XSQL Page Processor to add the yourstyle.xsl (relative) stylesheet reference to the (possibly empty) set of <?xml-stylesheet?> processing instructions at the top of the list so it will be used instead of ones that are provided by default in the file (or will be used instead of not using one if none was indicated by default in the .xsql itself).

If you want to force the .xsql servlet processor to completely ignore the processing of <?xml-stylesheet?> processing instructions and hence return the raw XML output of the .xsql page, specify an xml-stylesheet URL parameter equal to none as follows.

http://yourserver/yourdatapage.xsql?param1=value&xml-stylesheet=none

This can be useful during development.

You can supply the additional transform=client or transform=server in the URL after providing a xml-stylesheet URL-parameter to indicate whether you want the URL-supplied stylesheet to be processed in the server or on the client. The default behavior if the transform is not supplied is that the stylesheet gets processed in the server.


Note :

Any XSQL page can prevent externally-supplied stylesheets from being considered by the XSQL Page Processor by including the XML attribute allow-client-style="no" on the document element of that page. If this attribute is not present, or if its value is set to "y" or "yes", then externally supplied transformations are considered for processing.



Note :

The XSQL Page Processor performs the same stylesheet processing mechanics regardless of whether your .xsql page includes a connection="connname" attribute on the document element. This means you can use an .xsql page for server-side XSLT formatting of static XML content as well as (the much more interesting) dynamic SQL-query-based XML content.



Producing XML from SQL with Nested Structure

The XSQL Pages we've seen so far all show examples of using the <xsql:query> tag to include XML for flat query results. By "flat" we mean that each <ROW> element in the result contains a nested set of elements representing the scalar data for the columns returned in the query. None of the examples above demonstrate a query that returns a structured column.

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


Using User-Defined Object Types

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

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:

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.


Inserting XML Into Any Table You Require

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.


Overview

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:

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>
Next, let's say that you have a table called newsstory that you created with the following DDL statement.

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

We can either request this page through the webserver, or more conveninently for this job, use the XSQL Command Line Utility to quickly put the XSQL Page's output into a file:

xsql newstory.xsql newsstory.xml

This command processes the newsstory.xsql page above and writes the output to the file named newsstory.xml. The contents of the newsstory.xml file will look like:

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

We can take this one row of canonical output from a "SELECT *" query over the newsstory table and immediately turn it into our "insert transformation" by doing the following steps:

  1. Add an xsl namespace declaration to the document element,

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

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

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

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

The result of applying the changes outlined in the three steps above to the sample output of the:

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>

Which illustrates that we have:

If we rename the now-edited newsstory.xml file to moreover-to-newsstory.xsl, we can use the command-line oraxsl utility to test our transformation:

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

The general steps for inserting XML into the database are:

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

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

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

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

Oracle XSQL Pages support a simple <xsql:insert-request> tag that automates these four steps for you when you need to post XML documents to be inserted into the database to your webserver over HTTP. Given the name of the table or view to use as the insert target and the name of the XSL transformation to use to transform the inbound document into the canonical format for this table or view, you can add the tag:

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

to the top of your XSQL page to perform the four steps above automatically. So, for example, the following XSQL Page would accept information posted through HTTP in the Moreover.com moreovernews format, and insert it into the newsstory table:

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

Running this program retrieves the newsstories and inserts them into our NEWSSTORY table.

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

Then we can create the INSTEAD OF INSERT trigger from the SQL*Plus command line using code like:

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;

Here we are assuming that "uniqueness" of a story is defined by the combination of its TITLE and its URL columns. To make the check fast, we can create a unique index on the (TITLE,URL) combination with the command:

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

We've written the body of the trigger in PL/SQL to demonstrate that you can mix and match PL/SQL and Java in this solution, but in Oracle8i we could have also written the INSTEAD OF trigger to call a Java Stored Procedures as well to perform the uniqueness check.

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

Now, only unique newstories from the Moreover XML news feed will be inserted.


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>

The XSQL page above inserts any posted XML document containing moreovernews/article elements (as we did programmatically above), and then returns the XML datagram to the requester that looks like this viewed "raw" in the Internet Explorer 5.0 browser:

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>

and then allows an <xsql:insert-request> tag to treat this document as the posted input document.


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

and by referencing the name of the request-to-newsstoryview.xsl transform that looks like:

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

If the above XSQL page were saved as insertnewsform.xsql, it can be used as the target of an HTML <FORM> element that includes fields named title_field and url_field by simply setting the forms's ACTION="insertnewsform.xsql" attribute. The following newsform.html file demonstrate this:

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

If we let a user fill-out and post the form as-is, they will get raw XML as a response from the insertnewform.xsql page, listing the five most recent news stories entered as show above. However, we can easily improve on that for better usability.

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>

This means the insertnewsform.xsql now will look like:

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

Now when the user browses the newsform.html page and enters her story...

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



Using the XSQL Command-Line Processor

You can process any XSQL page at the command line by using the XSQLCommandLine class, sending the output to standard out or to a filename that your provide.

java oracle.xml.xsql.XSQLCommandLine xsqlURI [outputFile] [param1=value1 ... paramN=valueN]

The .\bin\xsql.bat DOS batch file and .\bin\xsql shell script are provided as a convenience for running the command line utility. It sets up the CLASSPATH correctly to contain all of the JAR files required (same ones listed for the Servlet above), then calls jre to run the oracle.xml.xsql.XSQLCommandLine class.

xsql xsqlURI [outputFile] [param1=value1 ... paramN=valueN]

So, for example, to test the demo file airport.xsql you can do:

xsql airport.xsql airport=sfo xml-stylesheet=none
to output the results of processing the airport.xsql to standard output, or:
xsql airport.xsql output.html airport=sfo
to output the results to the output.html.

To post an XML Document to an XSQL Page being processed by the XSQLCommandLine utility, provide a URL or filename as the value of the posted-xml command-line parameter like:

xsql insertnewsstory.xsql 
     posted-xml=http://p.moreover.com/cgi-local/page?index_xml+xml

This will use the XSQL Page named insertnewsstory.xsql to insert the XML contents retrieved by visiting the URL above.


Built-in Action Handler Reference

The following is a list of all built-in Action Elements that you can use in your XSQL Pages. ..

Action Element Description
<xsql:query> Execute an arbitrary SQL statement and include its result set in canonical XML format.
<xsql:dml> Execute a SQL DML statement or PL/SQL anonymous block.
<xsql:set-stylesheet-param> Set the value of a top-level XSLT stylesheet parameter. Value of the parameter can be set by supplying the optional "value" attribute, or by including a SQL statement as the element content.
<xsql:insert-request> Insert the (optionally transformed) XML document that has been posted in the request into a database table or view. If HTML Form has been posted, then posted XML document is materialized from HTTP request parameters, cookies, and session variables.
<xsql:include-xml> Include arbitrary XML resources at any point in your page by relative or absolute URL.
<xsql:include-request-params> Include key information like HTTP Parameters, Session Variable values and Cookies into your XSQL Page for addressing them in your stylesheet.
<xsql:include-xsql> Include the results of one XSQL Page at any point inside another.
<xsql:include-owa> Include the results of executing a stored procedure that makes use of the Oracle Web Agent (OWA) packages inside the database to generate XML.
<xsql:action> Invoke a user-defined action handler, implemented in Java, for executing custom logic and including custom XML information into your XSQL Page.
<xsql:ref-cursor-function> Includes the canonical XML representation of the result set of a cursor returned by a PL/SQL stored function.
<xsql:set-page-param> Set a page-level (local) parameter that can be referred to in subsequent SQL statements in the page. The value can be set using a static value, the value of another parameter, or the results of a SQL statement.
<xsql:include-param> Include a parameter and its value as an element in your XSQL page.
<xsql:set-session-param> Set an HTTP-Session level parameter. The value can be set using a static value, the value of another parameter, or the results of a SQL statement.
<xsql:set-cookie> Set an HTTP Cookie. The value can be set using a static value, the value of another parameter, or the results of a SQL statement.
<xsql:insert-param> Inserts the value of a single parameter containing XML. Can optionally supply a transform to get it in canonical format.

See the on-line help system for full details on each element. Additional notes on individual action elements follow below:


Advanced Topics


Modifying XSQL Configuration Settings

The XSQL Page Processor loads the first XSQLConfig.xml file that it can find in the CLASSPATH of the environment in which it is running. The XSQLConfig.xml file contains numerous configuration parameters whose values affect how the XSQL Page Processor and XSQL Servlet do their job. Each configuration parameter is fully documented in comments appearing immediately before it in the XSQLConfig.xml file. By default, the XSQLConfig.xml file is found in the ./xsql/lib directory.


Using the XSQL Page Processor Programmatically

The oracle.xml.xsql.XSQLRequest class is provided to make use of the XSQL Page Processor from within your own Java programs. The following is a simple example of putting it to use...

import oracle.xml.xsql.XSQLRequest;
import java.util.Hashtable;
import java.io.PrintWriter;
import java.net.URL;

public class XSQLRequestSample {
  public static void main( String[] args) throws Exception {

    // Construct the URL of the XSQL Page
    URL pageUrl = new URL("file:///C:/foo/bar.xsql");

    // Construct a new XSQL Page request
    XSQLRequest req = new XSQLRequest(pageUrl);

    // Setup a Hashtable of named parameters to pass to the request
    Hashtable params = new Hashtable(3);
    params.put("param1","value1");
    params.put("param2","value2");

    /* If needed, treat an existing, in-memory XMLDocument as if
    ** it were posted to the XSQL Page as part of the request

       Example:
       --------
       req.setPostedDocument(myXMLDocument);

    **
    */

    // Process the page, passing the parameters and writing the output
    // to standard out.
    req.process(params,new PrintWriter(System.out)
                      ,new PrintWriter(System.err));

  }
}

You can also call processToXML() instead of process() to return the XML Document in-memory that results from processing the XSQL Page requested and applying any associated XSLT Transformation.


Exploiting the Available Diagnostics


Dumping XSQL Connection Pool Information

If you set the <dump-allowed> element to the value yes in the XSQLConfig.xml file as shown below...

<XSQLConfig>
  :
  <processor>
    :
    <connection-pool>
      :
      <dump-allowed>yes</dump-allowed>
    </connection-pool>
    :
  </processor>
  :
</XSQLConfig>

...then you can request diagnostic information about the XSQL Servlet's connection pooling and named connection definitions from a browser.

To request an XSQL Servlet connection pool diagnostic dump, add the extra URL parameter dumppool=y to the end of your URL request for any .xsql page.

http://yoursever/your.xsql?dumppool=y


Requesting Timing Information

If you set the <page> or <action> element(s) to the value yes in the XSQLConfig.xml file as shown below...

<XSQLConfig>
  :
  <processor>
    :
    <timing-info>
      <page>yes</page>
      <action>no</action>
    </timing-info>
    :
  </processor>
  :
</XSQLConfig>

...then the requested level of timing information (in milliseconds) will be included in the XSQL Page by the page processor. Page-level timing information is included as an XML attribute named xsql-timing="123" that is added to the document element of your XSQL Page before any associated XSLT transformation is applied (if any). Action-level timing information is included as XML comments immediately before the results of that action in the page.


Writing a Custom XSQL Action Handler

The XSQL Page Processor processes an XSQL Page by:

Oracle XSQL Pages come with a number of built-in action handlers for the following basic XSQL action elements:

Built-in XSQL Action Elements and Action Handler Classes
XSQL Action Element Handler Class in oracle.xml.xsql.actions
<xsql:query> XSQLQueryHandler
<xsql:dml> XSQLDMLHandler
<xsql:set-stylesheet-param> XSQLStylesheetParameterHandler
<xsql:insert-request> XSQLInsertRequestHandler
<xsql:update-request> XSQLUpdateRequestHandler
<xsql:delete-request> XSQLDeleteRequestHandler
<xsql:include-xml> XSQLIncludeXMLHandler
<xsql:include-request-params/> XSQLIncludeRequestHandler
<xsql:include-xsql> XSQLIncludeXSQLHandler
<xsql:include-owa> XSQLIncludeOWAHandler
<xsql:action> XSQLExtensionActionHandler
<xsql:ref-cursor-function> XSQLRefCursorFunctionHandler
<xsql:include-param> XSQLGetParameterHandler
<xsql:set-session-param> XSQLSetSessionParamHandler
<xsql:set-page-param> XSQLSetPageParamHandler
<xsql:set-cookie> XSQLSetCookieHandler
<xsql:insert-param> XSQLInsertParameterHandler

Action Handlers are initialized by getting passed the XSQLPageRequest object for context and the root node of a DOM DocumentFragment to which the action handler should append any dynamically created elements or other DOM Nodes. The XSQL Page Processor replaces the action element in the template page with content of the DocumentFragment created by the appropriate Action Handler for the element.

To create a custom Action Handler, you need to provide a class that implements the oracle.xml.xsql.XSQLActionHandler interface.

For convenience, you can save time by extending the base implementation class named oracle.xml.xsql.XSQLActionHandlerImpl that provides a default implementation of the init() method and offers a set of useful helper methods that should prove useful.


Note :

If you extend oracle.xml.xsql.XSQLActionHandlerImpl and override the init() method to gather information from the attributes or element content of the action element in the template, make sure to call:

// Call the superclass to intialize protected variables
super(env,e);


For two examples of custom action handlers, see the:

The following example shows a custom action handler MyIncludeXSQLHandler that leverages one of the built-in action handlers and then uses arbitrary Java code to modify the resulting XML fragment returned by that handler before appending its result to the XSQL page:

import oracle.xml.xsql.*;
import oracle.xml.xsql.actions.XSQLIncludeXSQLHandler;
import org.w3c.dom.*;
import java.sql.SQLException;

public class MyIncludeXSQLHandler extends XSQLActionHandlerImpl {
  XSQLActionHandler nestedHandler = null;
  public void init(XSQLPageRequest req, Element action) {
    super.init(req, action);
    // Create an instance of an XSQLIncludeXSQLHandler
    // and init() the handler by passing the current request/action
    // This assumes the XSQLIncludeXSQLHandler will pick up its
    // href="xxx.xsql" attribute from the current action element.
    nestedHandler = new XSQLIncludeXSQLHandler();
    nestedHandler.init(req,action);
  }
  public void handleAction(Node result) throws SQLException {
    DocumentFragment df = result.getOwnerDocument().createDocumentFragment();
    nestedHandler.handleAction(df);
    // Custom Java code here can work on the returned document fragment
    // before appending the final, modified document to the result node.
    // For example, add an attribute to the first child
    Element e = (Element)df.getFirstChild();
    if (e != null) {
      e.setAttribute("ExtraAttribute","SomeValue");
    }
    result.appendChild(df);
  }
}

Lastly, note that if you create custom action handlers that need to work differently based on whether the page is being requested through the XSQL Servlet, or the XSQL Command Line Utility, or programmatically through the XSQLRequest class, then in your Action Handler implementation you can call getPageRequest() to get a reference to the XSQLPageRequest interface for the current page request. By calling getRequestType()on the XSQLPageRequest object, you can see if the request is coming from the "Servlet", "Command Line", or "Programmatic" routes respectively. If the return value is "Servlet", then you can get access to the HTTP Servlet's request and response objects by:

XSQLServletPageRequest xspr = (XSQLServletPageRequest)getPageRequest();
if (xspr.getRequestType().equals("Servlet")) {
  HttpServletRequest     req  = xspr.getHttpServletRequest();
  HttpServletResponse   resp  = xspr.getHttpServletResponse();
    // do something fun here with req and resp, however
    // writing to the response directly from a handler will
    // produce unexpected results. Allow the XSQL Servlet
    // to write to the servlet's response output stream
    // at the write moment later when all action elements
    // have been processed.
}


Using a Custom XSQL Action Handler in an XSQL Page

To include an Action Element in your XSQL Page that invokes a custom Action Handler that is implemented by the yourpackage.YourCustomActionHandler class, add an element like:

<xsql:action handler="yourpackage.YourCustomHandler"/>

or

<xsql:action handler="yourpackage.YourCustomHandler" param="xxx"/>

or

<xsql:action handler="yourpackage.YourCustomHandler" param="xxx">
  Any text or <element>content</element>
</xsql:action>


Defining Custom XSQL Action Element for your Handler

For additional convenience, you can define entries in the XSQLConfig.xml file that associate an action element name and a handler class. So, for example, in the default XSQLConfig.xml file shipped with Oracle XSQL Pages, you'll see:

 <actiondefs>
    <action>
      <elementname>param</elementname>
      <handlerclass>oracle.xml.xsql.actions.ExampleGetParameterHandler</handlerclass>
    </action>
    <action>
      <elementname>current-date</elementname>
      <handlerclass>oracle.xml.xsql.actions.ExampleCurrentDBDateHandler</handlerclass>
    </action>
  </actiondefs>

These entries in the config file allow a page to use:

<xsql:param name="myparam"/>

instead of the more verbose:

<xsql:action handler="oracle.xml.xsql.actions.ExampleGetParameterHandler"
                name="myparam"/>

and similarly

<xsql:current-date/>

instead of the more verbose:

<xsql:action handler="oracle.xml.xsql.actions.ExampleDBDateHandler"/>


Demos Included with This Release

The following demo files are part of this release. Instructions for installing the demos are included in the Installation section of these release notes.
Description of Demonstrations
Demonstration Name (directory) Comments
Hello World (helloworld) Simplest possible XSQL page.
Do You XML Site (doyouxml)

XSQL page which shows how a simple, data-driven web site can be built using an XSQL page which makes clever use of SQL, XSQL-substitution variables in the queries, and XSLT for formatting the site.

Demonstrates using substitution parameters in both the body of SQL query statements within <xsql:query> tags, as well as withing the attributes to <xsql:query> tags to control things like how many records to display and to skip (for "paging" through query results in a stateless way).

Employee Page (emp)

XSQL page showing XML data from the EMP table, using XSQL page parameters to control what employees are returned and what column(s) to use for the database sort. Uses an associated XSLT Stylesheet for format the results as an HTML Form containing the emp.xsql page as the form action so the user can refine their search criteria.

Insurance Claim Page (insclaim)

Demonstrates a number of sample queries over the richly-structured, Insurance Claim object view. The insclaim.sql sets up the INSURANCE_CLAIM_VIEW object view and populates some sample data.

Invalid Classes Page (classerr)

XSQL Page which uses invalidclasses.xsl to format a "live" list of current Java class compilation errors in your schema. The accompanying .sql script sets up the XSQLJavaClassesView object view used by the demo. The master/detail information from the object view is formatted into HTML by the invalidclasses.xsl stylesheet in the server.

Airport Code Validation (airport)

XSQL page which returns a "datagram" of information about airports based on their three-letter code. Demonstrates using the <xsql:no-rows-query> to attempt alternative queries when initial queries return no rows. In this case, after attempting an exact match on the airport code passed in, the page tries a fuzzy match on the airport description.

The airport.htm page demonstrates how to use the XML results of the airport.xsql page programmatically from within a web page using JavaScript to exploit the built-in XML Document Object Model functionality in the Internet Explorer 5.0 browser.

When you type in a three-leter airport code into this web page, some JavaScript under the covers fetches the XML datagram from the XSQL Servlet over the web corresponding to information for the airport code you typed in. If the return indicates that there was no exact match, the builds up a dynamic "picklist" of possible matches based on the information returned in the XML "datagram" from the XSQL Servlet.

Airport Code Display (airport)

Demonstrates using the same XSQL page as the previous example but supplying an XSLT Stylesheet name in the request. This causes the airport information to be formatted as an HTML form instead of being returned as raw XML.

Emp/Dept Object View Demo (empdept) Demonstrates using an object view to group master/detail information from two existing "flat" tables like EMP and DEPT. The empdeptobjs.sql script creates the object view (along with INSTEAD OF INSERT triggers allowing the master/detail view to be used as an insert target of xsql:insert-request).

The empdept.xsl stylesheet illustrates an example of the "simple form" of an XSLT stylesheet that can look just like an HTML page without the extra xsl:stylesheet or xsl:transform at the top. This is part of the XSLT 1.0 specification called using a Literal Result Element as Stylesheet. It also demonstrates how to generate an HTML page that includes the <link rel="stylesheet"> to allow the generated HTML to fully leverage CSS for centralized HTML style information, found in the coolcolors.css file.

Adhoc Query Visualization (adhocsql) Demonstrates passing the entire SQL query and XSLT Stylesheet to use as parameters to the server.
XML Document Demo (document) Demonstrates inserting XML documents into relational tables. The docdemo.sql script creates a user-defined type called XMLDOCFRAG containing an attribute of type CLOB.

Try inserting the text of the document in ./xsql/demo/xml99.xml and providing the name xml99.xsl as the stylesheet, as well as ./xsql/demo/JDevRelNotes.xml with the stylesheet relnotes.xsl.

The docstyle.xsql page illustrates an example of the <xsql:include-xsql> action element to include the output of the doc.xsql page into its own page before transforming the final output using a client-supplied stylesheet name.

The demo uses the client-side XML features of Internet Explorer 5.0 to check the document for well-formedness before allowing it to be posted to the server.

XML Insert Request Demo (insertxml) Demonstrates posting XML from a client to an XSQL Page that handles inserting the posted XML information into a database table using the <xsql:insert-request> action element. The demo is setup to accept XML documents in the moreover.com XML-based news format.

In this case, the program doing the posting of the XML is a client-side web page using Internet Explorer 5.0 and the XMLHttpRequest object from JavaScript. If you look at the source for the insertnewsstory.xsql page, you'll see it's specifying a table name and an XSLT Transform name. The moreover-to-newsstory.xsl stylesheet transforms the incoming XML information into the canonical format that the OracleXMLSave utility knows how to insert.

Try copying and pasting the example <article> element several times within the <moreovernews> element to insert several new articles in one shot.

The newsstory.sql script shows how INSTEAD OF triggers can be used on the database views into which you ask XSQL Pages to insert to the data to customize how incoming data is handled, default primary key values, etc.

SVG Demo (svg) The deptlist.xsql page displays a simple list of departments with hyperlinks to the SalChart.xsql page. The SalChart.xsql page queries employees for a given department passed in as a parameter and uses the associated SalChart.xsql stylesheet to format the result into a Scalable Vector Graphics drawing, a bar chart comparing salaries of the employees in that department.
Then, browse the URL http://localhost/xsql/index.html to see a list of all the demos.


Closing Comments

Once you've understood what the XSQL Page Processor running inside the XSQL Servlet does, you'll realize that Oracle XSQL Pages is really just a very thin layer of convenience functionality allowing you to leverage the tremendous flexibility and power of SQL, XML, and XSLT to really do the "heavy lifting".


Note :

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