Oracle XSQL Pages and the XSQL Servlet

Release Notes for Version 1.0.0.0 (Production)

Contents

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

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

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

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

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

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

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

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

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

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

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

The two key design goals of Oracle XSQL Pages are:

• Make simple things very simple and hard things possible

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

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

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

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

you might write an XSQL Page like:

<?xml version="1.0"?>
<xsql:query connection="demo" 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:

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

• Using the Oracle XSQL Command Line Utility in batch programs,

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

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

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

Using this technology preview, 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.

The new features and enhancements in this release are based squarely on an incredible amount of user-feedback from our Oracle Technet XML Discussion Forum, where XSQL Page users post questions and feature ideas. We hope you like what's in this release since, in the best possible interpretation of the phrase, "You asked for it!"

• Get Started Quickly with XSQL Servlet on Windows with Web-to-Go Web Server

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

• New Online Help System

  This release includes a new, 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

• New Built-in XSQL Action Elements

  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:ref-cursor-function>	Includes the canonical XML representation of the result set of a cursor returned by a PL/SQL stored function. Using the new dynamic SQL features that are built-in to PL/SQL in Oracle8i, this means that
  <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.

In addition, numerous existing features have been enhanced and made more performant. Read on...

• You can now use parameters to provide the page-level connection name. So, you can now say:

  <MyPage xmlns:xsql="urn:oracle-xsql" connection="{@conn}">

  to allow the name of the connection to be provided in the request.

• You can now specify a fetch-size="nn" attribute on the <xsql:query> action element to control the number of records retrieved in each round trip to the database. This overrides the default fetch size set in the XSQLConfig.xml file for that query.

• You can now specify a reparse="yes" attribute on the <xsql:include-xsql> action element to force the output of a previous XSQL Page to be reparsed before including it into the data page of the current XSQL Page. This is useful if the previous page is retrieving text chunks from the database containing XML markup which you would like to parse and treat as elements in the including page.

• You can now specify a date-format attribute on <xsql:insert-request> and <xsql:insert-param> to control the format mask used to interpret date values during the insert of XML information into the database. The list of valid date formats that are accepted is documented in the JavaDoc for the java.text.SimpleDateFormat class, available at http://java.sun.com/products/jdk/1.1/docs/api/java.text.SimpleDateFormat.html

• The XSQLCommandLine utility can now post XML to the XSQL page it is processing (typically, for inserting into the database) by specifying the filename or URL of the XML to be posted as the value of the posted-xml command line parameter. For example, posted-xml=filename or posted-xml=http://somesite/someurl are both valid.

• Custom action handlers may now refer to the content of the posted XML document any number of times necessary when calling getPageRequest(). getPostedDocument(). Previously, the second time calling this returned null.

• The XSQLRequest class, the programmatic API to process XSQL Pages from within Java programs, now has a processToXML() method, allowing you to process the XSQL Page and get it's output as a DOM Document object, ready for subsequent processing by your program.

• A Unix shell script xsql has been added for convenience to access the XSQL Command line utility on Unix platforms. It is located in ./xsql/bin

• The version="1.0" attribute on <xsl:stylesheet> and the xsl:version="1.0" attribute on a literal-result element stylesheet are both now strictly enforced as per the XSLT 1.0 Specification by the Oracle XSLT Engine, version 2.0.2.7, which accompanies this release of XSQL Pages. Existing stylesheets which do not properly specify their version number will need to be updated to avoid the error that this new enforcement will raise when the stylesheet is loaded.

• The XSQL named parameter mechanism has changed slightly to integrate new support for referencing the values of HTTP Cookies. XSQL provides a single way to refer to values that can be specified as:

  • HTTP Request Parameters

  • HTTP Cookies

  • HTTP Session Variables

  • Local XSQL Page Parameters

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

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

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

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

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

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

  1. An XSQL local page parameter

  2. An HTTP Cookie

  3. An HTTP Session Variable

  4. An HTTP Request Parameter

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

  1. An XSQL local page parameter

  2. An XSQL Request parameter

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

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

• The <xsql:stylesheet-param> action element has been renamed to <xsql:set-stylesheet-param> for consistency with the other parameter-related actions.

• The XSQLConfig.xml file entry that controls whether connection-pool status "dumping" is allowed or not has changed. Previously, you specified:

  <XSQLConfig>
    :
    <connectiondefs dumpallowed="yes">
    :
    </connectiondefs>
  </XSQLConfig>

  Now you specify:

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

A number of new configuration options are now available in the XSQLConfig.xml file, where you can set...

• The default number of records to fetch per database round-trip for <xsql:query> actions.

• The size (in pages) of the XSQL Page LRU cache.

• The size (in stylesheets) of the XSLT Stylesheet LRU cache.

• The size (in bytes) of the Buffered Output Stream to use for XSQL Servlet (or zero to disable)

• The initial and increment size of the connection pool, as well as the timeout threshold (in seconds) for cleaning up connections.

• The initial and increment size of the XSLT Stylesheet pool, as well as the timeout threshold (in seconds) for cleaning up stylesheet instances.

• The MIME types for which to suppress setting character-set information.

• Whether or not to reload named connection definitions when an unknown connection name is requested.

• Whether or not to include per-page and/or per-action-element timing information in the XSQL Page output.

For more information on these settings, see the in-line comments in the XSQLConfig.xml file.

• Code optimizations have been implemented throughout.

• A new XSLT Stylesheet pooling mechanism offers significant improvement in performance and throughput on multithreaded Servlet engines.

• New LRU Caching algorithm (with tunable cache size) gives you better control over the memory used by XSQL Page processing.

• When using the Oracle JDBC driver, the XSQL page processor takes advantage of bulk record fetching to reduce network round-trips between web server and database. A default fetch size can be specified in XSQLConfig.xml, and any xsql:query action element can provide an overriding fetch size using the new fetch-size attribute.

• Performance of <xsql:include-xsql> has been improved dramatically.

• Demo files have been organized into individual subdirectories under ./xsql/demo

• A new "SVG Demo" has been added that demonstrates a simple example of using XSQL Pages to query database information and transform it using XSLT into Scalable Vector Graphics.

  
  

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

  
  

This technical preview release contains the following known issues:

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

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

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

The XSQL Pages and XSQL Servlet have been tested using:

• JDK 1.1.8

• JDK 1.2.2

These are the only two 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.

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

• Allaire JRun 2.3.3

• Apache 1.3.9 with JServ 1.0 and 1.1

• Apache 1.3.9 with Tomcat 3.1 Beta1 Servlet Engine

• Apache Tomcat 3.1 Beta1 Web Server + Servlet Engine

• Caucho Resin 1.1

• NewAtlanta ServletExec 2.2 for IIS/PWS 4.0

• Oracle8i Lite Web-to-Go Server

• Oracle Application Server 4.0.8.1 (with "JSP Patch")

• Oracle8i 8.1.7 Beta "Aurora" Servlet Engine

• Sun JavaServer Web Development Kit (JSWDK) 1.0.1 Web Server

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

• Apache 1.3.9 with Tomcat 3.1 Beta1 Servlet Engine

• Apache Tomcat 3.1 Beta1 Web Server + Tomcat 3.1 Beta1 Servlet Engine

• Caucho Resin 1.1 (Built-in JSP 1.0 Support)

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

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

• Oracle8i 8.1.7 Beta "Aurora" Servlet Engine with Oracle JSP 1.0

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

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

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

• Oracle8i 8.1.5 Driver for JDBC 1.x

• Oracle8i 8.1.6 Driver for JDBC 1.x

• Oracle8i Lite 4.0 Driver for JDBC 1.x

• Oracle8i 8.1.6 Driver for JDBC 2.0

Note : At this time, the <xsql:insert-param> and <xsql:insert-request> action elements depend on the Oracle JDBC Driver because they depend on the Oracle XML SQL Utility for Java which at present exhibits this restriction. A future release of Oracle XML SQL will remove this current restriction.

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.

Oracle XSQL Pages depends on:

• Oracle XML Parser V2 Release 2.0.2.7

• Oracle XML SQL Utilities for Java

• A Web Server that supports Java Servlets

• A JDBC driver, like Oracle JDBC or Oracle8i Lite JDBC

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

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

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_0_0.tar.gz

   on Unix, or on Windows:

   pkzip25 -extract -directories xsqlservlet_v1_0_0_0.zip

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

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.

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>

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 : For convenience, the xsqlservlet_v1_0_0_0.tar.gz and xsqlservlet_v1_0_0_0.zipdistributions 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)

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:

   C:\xsql\lib\xmlparserv2.jar;
   C:\xsql\lib\oraclexmlsql.jar;
   C:\xsql\lib\classes111.zip;
   C:\xsql\lib\oraclexsql.jar;
   C:\xsql\lib

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

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:

   # Oracle XSQL Servlet
   wrapper.classpath=C:\xsql\lib\oraclexsql.jar
   # Oracle JDBC (8.1.6)
   wrapper.classpath=C:\xsql\lib\classes111.zip
   # Oracle XML Parser V2 (with XSLT Engine)
   wrapper.classpath=C:\xsql\lib\xmlparserv2.jar
   # Oracle XML SQL Components for Java
   wrapper.classpath=C:\xsql\lib\oraclexmlsql.jar
   # XSQLConfig.xml File location
   wrapper.classpath=C:\xsql\lib
   

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

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 Beta 1 comes pre-configured with the following contexts (defined by <Context> entries in the ./jakarta-tomcat/conf/server.xml file)...

   • The root context

   • /examples

   • /test

   • /admin

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

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

   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

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

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.

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>

<?xml  version = '1.0'?>
<ROWSET>
   <ROW id="1">
      <GREETING>Hello World</GREETING>
   </ROW>
</ROWSET>

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>

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

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

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

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.

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:

• Provides a literal example of the target document's structure, and

• Includes special XSLT tags like <xsl:value-of> and <xsl:for-each> that indicate where information in the source document should be "plugged" into the target document.

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.

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

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:

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 browser is Lynx, use doyouxml-lynx.xsl to transform the document in the server

• If the browser is Internet Explorer 5.0, use doyouxml-ie5.xsl to transform the document in the client/browser

• Otherwise, use doyouxml.xsl to transform the document in the server

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.

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

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.

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

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

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

Requesting this

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

produces the resulting XML "datapage":

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

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

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

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

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

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

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

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

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

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

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

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

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

• An arbitrary XML document X, and

• A database table T where you want to insert it

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

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

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

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

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

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

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

So, we need to produce an XSL transformation that transforms the information in the XML news feed from Moreover.com into 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:

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

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

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

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

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

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

• Changed the:

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

  tags to:

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

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

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

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;

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.

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

xsql airport.xsql output.html airport=sfo

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.

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

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

• If you will be using the <xsql:include-owa> action element, you may need to install the latest OWA Packages for PL/SQL if you do not have them installed already. If you determine they need to be installed, see the ./xsql/demo/owa/owa_packages.zip file included in this distribution and execute the owains.sql script to create the necessary packages.

• If you run the XSQL Servlet from inside a corporate firewall, then you will need to uncomment the <http> element in the XSQLConfig.xml file and specify an appropriate host name in the <proxyhost> element. Failure to do this will result in a "cannot reach destination" error when the XSQL Page Processor tries to process the <xsql:include-xml> action element in your page for URL's outside your corporate firewall.

• The results of the XML resource included by an <xsql:include-xml> element must be well-formed XML to be included, otherwise an error will be signalled to this effect.

• The format of the XML document fragment that is included when you use <xsql:include-request-params> is:

  <request>
    <parameters>
      <param1>value1</param1>
      <param2>value2</param2>
         :
    </parameters>
    <session>
      <name1>val1</name1>
      <name2>val2</name2>
         :
    </session>
    <cookies>
      <cookiename1>value1</cookiename1>
         :
    </cookies>
  </parameters>

  For the HTTP case through the XSQL Servlet, and

  <request>
    <parameters>
      <param1>value1</param1>
      <param2>value2</param2>
         :
    </parameters>
  </parameters>

  for other types of requests like through the XSQL Command Line Utility, or through the programmatic XSQLRequest class.

• The <xsql:insert-request> action element supports both HTTP-Posted XML documents as well as HTTP-Posted HTML Forms. If an HTML Form is posted, the servlet creates an XML document of the form:

  <request>
    <parameters>
      <param1>value1</param1>
         :
      </paramN>valueN</paramN>
    </parameters>
       :
  </request>

• To determine the canonical format of the XML document needed for automatic insert into a table or view using <xsql:insert-request> or <xsql:insert-param> , use an XSQL Page to do a

  SELECT * FROM TABLE WHERE ROWNUM < 2

  query from the table/view in question and use it's output as the template for the canonical format.

• Errors raised by the processing of any XSQL Action Elements are now reported as XML elements in a uniform way so XSL Stylesheets can easily detect the presence of errors and optionally format them for presentation if desired.

  The action element in error will be replaced in the page by:

  <xsql-error action="xxx">

  Depending on the error the <xsql-error> element will contain a nested <message> element and a <statement> element with the offending SQL statement.

  An example fragment of an XSLT stylesheet that would leverage this information to display error information on the screen would look like:

  <xsl:if test="//xsql-error">
    <table style="background:yellow">
    <xsl:for-each select="//xsql-error">
      <tr>
        <td><b>Action</b></td>
        <td><xsl:value-of select="@action"/></td>
      </tr>
      <tr valign="top">
        <td><b>Message</b></td>
        <td><xsl:value-of select="message"/></td>
      </tr>
    </xsl:for-each>
    </table>
  </xsl:if>

• If your stylesheet uses an <xsl:output> element, the XSQL Page Processor will infer the media type and encoding of the returned document from the media-type and encoding attributes of <xsl:output> .

• JSP pages can use <jsp:forward> to forward to an XSQL Page and <jsp:include> to include the results of an XSQL Page.

• Attributes of the <xsql:query> tag (or any action element) can reference parameters in the same way the SQL statement for the tag can, allowing the client to pass in values for parameters like skip-rows or any other.

• Client-overriding of stylesheets for an XSQL Page can be disallowed with the new allow-client-style="no" on the document element of the page.

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.

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.

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

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.

The XSQL Page Processor processes an XSQL Page by:

• Looking for "Action Elements" from the xsql namespace, and for each one found,

• Invoking an appropriate action element handler class to process the element.

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

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:

• src/oracle/xml/xsql/actions/ExampleCurrentDBDateHandler.java

  Illustrates an example of an action handler that replaces the action element with the current value of the database SYSDATE, using the JDBC connection that is available from the XSQLPageRequest passed into the handler. The sample code illustrates that by calling:

  getPageRequest().getJDBCConnection()

  you can gain access to the current JDBC connection being used for this request. Note that the connection may be null if the page has not requested a connection.

• src/oracle/xml/xsql/actions/ExampleGetParameterHandler.java

  Illustrates a handler that adds an element to the XSQL page with the value of the indicated parameter. Shows a custom init() method that uses the value of the name attribute on the action element to determine the name of the parameter value to include in the result.

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

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>

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

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.

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

The goal of this Oracle XSQL Pages Technology Preview is to simplify the task of experimenting with SQL/XML/XSL data publishing and provide us feedback on where you'd like the technology to go to make your Oracle data work harder for you on the Web.

Mail any feedback, comments, or suggestions to Steve Muench and I'll make sure it gets to the right development team within Oracle.

Note : The explanatory and tutorial material included in this document is excerpted from my forthcoming book on Oracle and XML, due out sometime in the year 2000 from O'Reilly. Material has been included by permission.