Sign In Sign-Up

Welcome!

Close

Would you like to make this site your homepage? It's fast and easy...

Yes, Please make this my home page!

No Thanks

  Don't show this to me again.

Close

Oracle XSQL Pages and the XSQL Servlet

Release Notes for Version 1.0.2.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.

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

• Improve Database Performance with True Bind Variables Anywhere SQL Queries are Allowed

  In addition to the lexical (text-replacement) parameters that XSQL Pages have always supported, this release adds support for using true SQL statement bind variables. Using standard JDBC bind variable notation, you use a question mark to represent a variable whose value will be bound to the value of an XSQL parameter. JDBC bind variables are bound by position and the binding is specified by providing an ordered, space-separated list of XSQL parameter names using the new bind-params attribute like this:

  <page connection="demo" xmlns:xsql="urn:oracle-xsql">
    <xsql:query bind-params="id sal">
      SELECT * FROM EMP WHERE EMPNO = ? AND ? BETWEEN 1000 AND 4000
    </xsql:query>
  </page>

  Here's an example of binding three parameters in the function call for an <xsql:ref-cursor-function> action:

  <page connection="demo" xmlns:xsql="urn:oracle-xsql">
    <xsql:ref-cursor-function bind-params="id name choice">
      MyPackage.MyFunction(?,?,?) 
    </xsql:ref-cursor-function>
  </page>

  If you need to bind the same value multiple times, just refer to each occurrence with its own question-mark symbol and then repeat the name of the parameter in the appropriate sequence to match up with the question mark bind variable indicators.

  <page connection="demo" xmlns:xsql="urn:oracle-xsql">
    <xsql:query bind-params="id name id name">
      /* Get the row from the emp table */
      SELECT empno, ename
        FROM emp
       WHERE empno = ? /* BindVar 1 */ or ename = ? /* BindVar 2 */
      UNION ALL
      /* Or from the former_employee table */
      SELECT empno, ename
        FROM former_employee
       WHERE empno = ? /* BindVar 3 */ or ename = ? /* BindVar 4 */
    </xsql:query>
  </page>

  The question mark is the standard JDBC bind variable indicator. If you're using the Oracle JDBC driver, you can optionally use the named bind variable notation as an alternative. For example, using the numbers-as-names bind variables :1 and :2, the previous example can be written:

  <page connection="demo" xmlns:xsql="urn:oracle-xsql">
    <xsql:query bind-params="id name">
      /* Get the row from the emp table */
      SELECT empno, ename
        FROM emp
       WHERE empno = :1 /* BindVar 1 */ or ename = :2 /* BindVar 2 */
      UNION ALL
      /* Or from the former_employee table */
      SELECT empno, ename
        FROM former_employee
       WHERE empno = :1 /* BindVar 1, again */ or ename = :2 /* BindVar 2, again */
    </xsql:query>
  </page>

  Using the Oracle8i release 8.1.7 JDBC driver, you can also use string-based bind variable names. As with the numbers-as-names bind variables, the sequential order of named bind variables is defined by the order in which they first appear in the query. So the above example can be rewritten equivalently as:

  <page connection="demo" xmlns:xsql="urn:oracle-xsql">
    <xsql:query bind-params="id name">
      /* Get the row from the emp table */
      SELECT empno, ename
        FROM emp
       WHERE empno = :emp_id /* BindVar 1 */ or ename = :emp_name /* BindVar 2 */
      UNION ALL
      /* Or from the former_employee table */
      SELECT empno, ename
        FROM former_employee
       WHERE empno = :emp_id /* BindVar 1, again */ or ename = :emp_name /* BindVar 2, againa */
    </xsql:query>
  </page>

  
  

  Note: If any of the parameter names listed in the bind-params attribute have not been supplied a specific value, their value will be bound as a SQL NULL value.

  
  

  The following built-in action handlers support bind variables using the same bind-params attribute mechanism described above:

  • <xsql:query>

  • <xsql:ref-cursor-function>

  • <xsql:dml>

  • <xsql:include-owa>

  • <xsql:set-page-param>

  • <xsql:set-stylesheet-param>

  • <xsql:set-session-param>

  • <xsql:set-cookie>

  Because using true bind variables can improve the performance of your XSQL Pages' database interactions by allowing the database to reuse previously parsed cursors, it is recommended that you use true bind variables anywhere where SQL permits them. Be aware that true bind variables do not make XSQL lexical parameters obsolete in the least! Anywhere true bind variables are not allowed ? in the FROM clause, ORDER BY clause, or to substitute fragments of the query syntax ? the existing XSQL parameters are just what the doctor ordered.

• Set Default Date Format for <xsql:query> and <xsql:ref-cursor-function>

  You can now supply the date-format="formatmask" attribute on the <xsql:query> and <xsql:ref-cursor-function> action elements to control the default format mask for date values returned in queries. The valid format masks are those documented in the JavaDoc for the java.text.SimpleDateFormat class.

• Produce Custom Output From Your XSQL "Data Page" with Custom Serializers

  You can now provide a user-defined serializer class to programmatically control how the final XSQL datapage's XML document should be serialized to a text or binary stream. A user-defined serializer must implement the oracle.xml.xsql.XSQLDocumentSerializer interface which comprises the single method:

  void serialize(org.w3c.dom.Document doc, 
                 XSQLPageRequest      env) throws Throwable;

  In this release, DOM-based serializers are supported. A future release may support SAX2-based serializers as well. A custom serializer class is expected to perform the following tasks in the correct order:

  1. Set the content type of the serialized stream before writing any content to the output PrintWriter (or OutputStream).

     You set the type by calling setContentType() on the XSQLPageRequest that is passed to your serializer. When setting the content type, you can either set just a MIME type like this:

     env.setContentType("text/html");

     or a MIME type with an explicit output encoding character set like this:

     env.setContentType("text/html;charset=Shift_JIS");

  2. Call getWriter() or getOutputStream()(but not both!) in the XSQLPageRequest to get the appropriate PrintWriter or OutputStream respectively to use for serializing the content.

  For example, the following custom serializer (included in the XSQL distribution in ./xsql/src/oracle/xml/xsql/serializers/XSQLSampleSerializer.java) illustrates a simple implementation which simply serializes an HTML document containing the name of the document element of the current XSQL data page:

  package oracle.xml.xsql.serializers;
  import org.w3c.dom.Document;
  import java.io.PrintWriter;
  import oracle.xml.xsql.*;
  
  public class XSQLSampleSerializer
         implements XSQLDocumentSerializer {
  
    public void serialize(Document doc, XSQLPageRequest env) throws Throwable {
      String encoding = env.getPageEncoding();  // Use same encoding as XSQL page
                                                // template. Set to specific
                                                // encoding if necessary
                     
      String mimeType = "text/html";// Set this to the appropriate content type
  
      // (1) Set content type using the setContentType on the XSQLPageRequest
      if (encoding != null && !encoding.equals("")) {
        env.setContentType(mimeType+";charset="+encoding);
      }
      else {
        env.setContentType(mimeType);
      }
      // (2) Get the output writer from the XSQLPageRequest
      PrintWriter e = env.getWriter();
      // (3) Serialize the document to the writer
      e.println("<html>Document element is <b>"+
                doc.getDocumentElement().getNodeName()+
                "</b></html>");
    }
  
  }

  There are two ways to use a custome serializer, depending on whether you need to first perform an XSLT transformation before serializing or not. To perform an XSLT transformation before using a custom serializer, simply add the serializer="java:fully.qualified.ClassName" in the <?xml-stylesheet?> processing instruction at the top of your page like this:

  <?xml version="1.0?>
  <?xml-stylesheet type="text/xsl" href="mystyle.xsl" serializer="java:my.pkg.MySerializer"?>

  If you only need the custom serializer, simply leave out the type and href attributes like this:

  <?xml version="1.0?>
  <?xml-stylesheet serializer="java:my.pkg.MySerializer"?>

  You can also assign a short nickname to your custom serializers in the <serializerdefs> section of the XSQLConfig.xml file and then use the nickname (case-sensitive) in the serializer attribute instead to save typing. For example, if you have the following in XSQLConfig.xml:

  <XSQLConfig>
     <!-- etc. -->
    <serializerdefs>
      <serializer>
        <name>Sample</name>
        <class>oracle.xml.xsql.serializers.XSQLSampleSerializer</class>
      </serializer>
      <serializer>
        <name>FOP</name>
        <class>oracle.xml.xsql.serializers.XSQLFOPSerializer</class>
      </serializer>
    </serializerdefs>
  </XSQLConfig>

  then you can use the nicknames "Sample" and/or "FOP" as shown in the following examples:

  <?xml-stylesheet type="text/xsl" href="emp-to-xslfo.xsl" serializer="FOP"?>

  or

  <?xml-stylesheet serializer="Sample"?>

• Serialize XSQL Data Pages in Binary Formats

  The XSQLPageRequest interface now supports a getOutputStream() method as an alternative to the existing getWriter() method. Custom serializers can call getOutputStream() to return an OutputStream instance into which binary data (like a dynamically produced GIF image, for example) can be serialized. Using the XSQL Servlet, writing to this output stream results in writing the binary information to the servlet's output stream. See the included oracle.xml.xsql.serializers.XSQLSampleImageSerializer source code, which illustrates serializing a small GIF image, included in this release. Using the XSQL Command Line utility, the binary information is written to the target output file. Using the XSQLRequest programmatic API, two new methods have been added which allow the caller to supply the target OutputStream to use for the results of page processing. Note that your serializer must either call getWriter() (for textual output) or getOutputStream() (for binary output) but not both. Calling both in the same request will raise an error.

• Produce PDF Output from XSQL Pages via Integration with Apache FOP for XSL-FO

  Using the new support for custom serializers, the oracle.xml.xsql.serializers.XSQLFOPSerializer is provided as an example in this release for integrating with the Apache XML Project's FOP processor which formats an XML document containing XSL Formatting Objects and renders a PDF document from them. For example, given the following XSLT stylesheet, EmpTableFO.xsl:

  <!-- EmpTableFO.xsl -->
  <fo:root xmlns:fo="http://www.w3.org/1999/XSL/Format" xsl:version="1.0"
           xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <!-- defines the layout master -->
    <fo:layout-master-set>
      <fo:simple-page-master master-name="first" 
                             page-height="29.7cm" 
                             page-width="21cm" 
                             margin-top="1cm" 
                             margin-bottom="2cm" 
                             margin-left="2.5cm" 
                             margin-right="2.5cm">
        <fo:region-body margin-top="3cm"/>
      </fo:simple-page-master>
    </fo:layout-master-set>
    <!-- starts actual layout -->
    <fo:page-sequence master-name="first">
    <fo:flow flow-name="xsl-region-body">
        <fo:block font-size="24pt" font-family="Garamond" line-height="24pt" space-after.optimum="3pt" font-weight="bold" start-indent="15pt">
          Total of All Salaries is $<xsl:value-of select="sum(/ROWSET/ROW/SAL)"/>
        </fo:block>
  
        <!-- Here starts the table -->
        <fo:block border-width="2pt">
          <fo:table>
            <fo:table-column column-width="4cm"/>
            <fo:table-column column-width="4cm"/>
            <fo:table-body font-size="10pt" font-family="sans-serif">
              <xsl:for-each select="ROWSET/ROW">
                <fo:table-row line-height="12pt">
                  <fo:table-cell>
                    <fo:block><xsl:value-of select="ENAME"/></fo:block>
                  </fo:table-cell>
                  <fo:table-cell>
                    <fo:block><xsl:value-of select="SAL"/></fo:block>
                  </fo:table-cell>
                </fo:table-row>
              </xsl:for-each>
            </fo:table-body>
          </fo:table>
        </fo:block>
      </fo:flow>
    </fo:page-sequence>
  </fo:root>

  you can format the results of a query against the EMP table using the supplied FOP serializer (pre-defined in XSQLConfig.xml) with an XSQL page like:

  <?xml version="1.0"?>
  <?xml-stylesheet type="text/xsl" href="emptablefo.xsl" serializer="FOP"?>
  <xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
    SELECT ENAME, SAL FROM EMP
      ORDER BY SAL asc
  </xsql:query>

  
  

  Note: The example FOP Serializer provided with this release was tested against the 0.14.0 release of Apache FOP, and requires the following additional JAR files to be in your server-side CLASSPATH to function correctly: FOPHome/fop_bin_0_14_0.jar FOPHome/lib/w3c.jar The FOP release is available from the xml.apache.org website and the SAX2 release is available from http://www.megginson.com/SAX/Java/index.html. Since this is not a production-level release of FOP, the integration of XSQL/FOP should be considered experimental in this release until the FOP interface stabilizes in later releases.

  
  

• Optionally Use XSQL Pages with XML Parser 2.1.0.0 Beta

  XSQL Pages 1.0.2 is a production release and ships pre-configured with the latest production release of the Oracle XML Parser for Java, release 2.0.2.9, on which XSQL depends. You can use XSQL 1.0.2 with the Beta release of the Oracle XML Parser for Java, release 2.1.0.0, by including its xmlparserv2.jar archive in the CLASSPATH instead of the one that you will find in ./xsql/lib. The 2.1.0.0 Beta release of XML Parser for Java is included in ./xsql/lib/beta/xmlparserv2.jar for your convenience.

  
  

  Note: The Oracle XML Parser for Java, release 2.1.0.0, is a Beta release.

  
  

• Optionally Use XML SQL Utility 2.1.0.0 Beta

  XSQL Pages 1.0.2 is a production release and ships pre-configured with the latest production release of the Oracle XML SQL Utility for Java, release 1.2.1, on which XSQL depends. You can use XSQL 1.0.2 with the Beta release of the Oracle XML SQL Utility, release 2.1.0, by including its xsu12.jar archive in the CLASSPATH instead of the one that you will find in ./xsql/lib. The 2.1.0 Beta release of XML SQL Utility is included in ./xsql/lib/beta/xsu12.jar for your convenience.

  
  

  Note: The Oracle XML SQL Utility, release 2.1.0, is a Beta release.

  
  

• Insert, Update, Delete Via XML Using Non-Oracle JDBC Drivers

  If you are using XSQL Pages with the 2.1.0 Beta release of the XML SQL Utility in the CLASSPATH, the restriction in previous releases has been lifted so that the following XSQL actions that update the database based on posted XML documents:

  • <xsql:insert-request>

  • <xsql:insert-param>

  • <xsql:update-request>

  • <xsql:delete-request>

  can be used with any JDBC driver, not just the Oracle JDBC driver. Since other databases do not support object views, the types of XML that can be supported against these databases is limited to their flat, one-table-or-view capabilities.

• Produce Inline XML Schema for Queried Data

  If you are using XSQL Pages with the 2.1.0 Beta release of the XML SQL Utility in the CLASSPATH, you can provide the new include-schema="yes" attribute to either <xsql:query> or <xsql:ref-cursor-function> to produce XML results which include an inline XML Schema describing the XML resulting from the query. If you are using XSQL Pages with a version of the XML SQL Utility earlier than 2.1.0, this attribute is ignored.

• Request Parameters Missing in Command Line and XSQLRequest correctly

  When using the XSQL Command Line processor or the XSQLRequest API, pages processed that contained the <xsql:include-request-params> action were not properly reflecting any parameters passed in the request. Now both of these work correctly. This problem did not affect the servlet page processor.

• XSQL Command Line Pages Always Use UTF-8 Encoding

  Regardless of the setting of the <xsl:output> element's encoding attribute (or the encoding of the XSQL page template XML file for templates with no associated stylesheet) the XSQL Command Line processor always produced its output using the default character encoding. Now the appropriate encoding is used. This problem did not affect the servlet page processor.

  
  

  Note: To produce UTF-16 output, you must be using XSQL with a Java JDK version 1.3, which introduces UTF-16 support.

  
  

• REMOTE_ADDR CGI Variable Not Available via <xsql:include-owa>

  Now the REMOTE_ADDR variable is correctly passed to the OWA environment so that stored procedures can use OWA_UTIL.GET_CGI_ENV('REMOTE_ADDR') to retrieve the IP address of the requesting machine.

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

• Dynamic Stylesheet Assignment

  It is now possible to include one or more XSQL parameter references in the value of the href attribute for an <?xml-stylesheet?> processing instruction. This allows the uri for the XSLT stylesheet to be determined dynamically at runtime. The parameter value may be passed in from the client, as can any parameter value, or assigned to a page-private parameter whose value is set at runtime using the <xsql:set-page-param> action. The following example illustrates how to dynamically fetch the value of the stylesheet name to use from a table in the database:

  <?xml version="1.0"?>
  <?xml-stylesheet type="text/xsl" href="{@sheetname}.xsl"?>
  <!-- Example of dynamically setting the XSLT stylesheet name -->
  <page>
     <!-- Set value of page-private "sheetname" param to value fetched from DB -->
     <xsql:set-page-param name="sheetname">
        SELECT stylesheet_name
          FROM my_user_pref_table
         WHERE userid = '{@uid}' /* Assume 'uid' cookie value is user name */
     </xsql:set-page-param>
     <xsql:query>
       SELECT * FROM YOURTABLE WHERE ... /* Query here */
     </xsql:query>
  </page>

• Declaratively Update Rows in Tables or Views using XML

  Previously, users relied on <xsql:dml> to accomplish table updates. This release of XSQL adds a new action handler called <xsql:update-request> which offers a new, declarative option. It offers the parallel update functionality to complement the declarative insert functionality provided already with <xsql:insert-request>. The <xsql:update-request> works identically to the <xsql:insert-request>, in that the posted XML document (optionally transformed by an indicated XSLT stylesheet) will be processed by the underlying XML SQL Utility.

  The differences are:

  1. The <xsql:update-request> has an additional required attribute named key-columns whose value indicates the list of one or more column names to be used as the key to find the record to be updated. The value of the key-columns attribute is a space-separated (or comma-separated) list of column names. The value(s) of the column(s) listed as key-columns will be used in the incoming XML document to retrieve the existing row and all column values present in the canonical XML document being processed will be updated.

  2. The rows processed are updated, rather than inserted.

  To limit the list of columns whose values should be updated, provide the optional columns attribute with a space-separated (or comma-separated) list of column names. The default behavior if the columns attribute is not supplied or has an empty string as its value is to update all columns that appear in the incoming XML document.

• Declaratively Delete Rows in Tables or Views using XML

  Previously, users relied on <xsql:dml> to accomplish table row deletes. This release of XSQL adds a new action handler called <xsql:delete-request> which offers a new, declarative option. It offers the parallel delete functionality to complement the declarative insert and update functionality provided with <xsql:insert-request> and <xsql:update-request>. The <xsql:delete-request> works like to the <xsql:insert-request>, in that the posted XML document (optionally transformed by an indicated XSLT stylesheet) will be processed by the underlying XML SQL Utility.

  The differences are:

  1. The <xsql:delete-request> has an additional required attribute named key-columns whose value indicates the list of one or more column names to be used as the key to find the record to be deleted. The value of the key-columns attribute is a space-separated (or comma-separated) list of column names. The value(s) of the column(s) listed as key-columns will be used in the incoming XML document to retrieve the existing row and all column values present in the canonical XML document being processed will be updated.

  2. The rows processed are deleted, rather than inserted or updated.

• Limit List of columns to insert using <xsql:insert-request>

  Previously, an <xsql:insert-request>would insert null for columns missing in the posted XML document. To limit the list of columns whose values should be inserted, provide the optional columns attribute with a space-separated (or comma-separated) list of column names. If this new, optional attribute is not supplied, behavior remains as it was previously.

• Add Request-Scoped Objects to the XSQLPageRequest context

  Using the new methods setRequestObject() and getRequestObject() methods on the XSQLPageRequest interface, your custom action handlers can associate arbitrary Java objects with the page request context. This allows an object to be created in one action and then retrieved/accessed from another action on the same or nested page. If the object added to the page request context implements the optional XSQLRequestObjectListener interface, then the object will receive notifcation when page processing is completed so that it may clean up any internal resources it may be using.

• ServletContext now available on XSQLServletPageRequest

  The ServletContext can now be accessed from custom action handlers through the getServletContext() method on XSQLServletPageRequest.

• connection="connname" can optionally be xsql:connection

  For additional clarity, your XSQL pages can now indicate their optional database connection to be used using the namespace-qualified attribute xsql:connection="connname" as an alternative to the unqualified connection="connname" attribute on the document element.

The following reported bugs were fixed in this release:

• Pages included using xsql:include-xsql not always cached correctly

  In certain situations nested XSQL pages included using the <xsql:include-xsql> action were not being cached correctly, resulting in their being reread and reparsed from disk unnecessarily. Now all included pages are cached correctly.

• Unhelpful errors if XSQLConfig.xml file is not well formed

  Previously, the XSQL Page Processor (Servlet, Command Line, and via XSQLRequest) would fail unceremoniously if its configuration file XSQLConfig.xml was not well formed. Now, helpful errors are printed including line number information on where the parse error was encountered in the config file.

• Parameters in including page not visible in nested page with reparse=yes

  When XSQL pages were included using <xsql:include-xsql> with the reparse=yes option, parameters from the including page were not properly visible to the included page. Fixed.

• Error in Demos using XML Parser V2 version 2.0.2.9

  All errors in the XSQL demos, caused by more stringent error checking in the XSLT engine introduced in the 2.0.2.9 release, have been fixed.

• Insert transform stylesheets ignore stylesheet parameters

  Any XSLT stylesheet parameters set using <xsql:set-stylesheet-param> were not properly seen by the optional "insert transforms" used by <xsql:insert-request> and <xsql:insert-parameter>. Fixed.

• Run out of open cursors using CURSOR() expressions

  Actually a bug in the underlying XML SQL Utility (XSU). Fixed by upgrading the latest production version of XSU, release 1.2.1, now included in the XSQL distribution.

• <xsql:action> missing from online help.

  Information on <xsql:action> has been added to the online help.

• Infinite loop passing param={@param}

  Passing the value "{@paramname}" for a parameter named paramname caused an infinite loop.

• Stylesheet parameters lost if one parameter contained an apostrophe

  Passing a stylesheet parameter whose value contains an apostrophe would result in having all stylesheet parameters ignored. Now a stylesheet parameter's value can contains a single-quote or a double-quote, but not both, and if a value contains both only that one parameter's value will be ignored.

• Connections not freed to the connection pool under certain circumstances

  Previously if you were:

  • Using XSQLRequest to call processToXML() on an XSQL Page which did not have an associated XSLT stylesheet, or

  • Using <xsql:include-xsql> to include another XSQL page that uses a different database connection from the current page and the included page did not have an associated XSLT stylesheet

  the connection for the XSQLRequest-requested page as well as the <xsql:include-xsql>'d page was not freed correctly to the connection pool, resulting in too many open connections errors after time. Fixed.

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

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

http://yourmachine/xsql/index.html

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

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

• HTTP Request Parameters

• HTTP Cookies

• HTTP Session Variables

• Local XSQL Page Parameters

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

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

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

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

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

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

1. An XSQL local page parameter

2. An HTTP Cookie

3. An HTTP Session Variable

4. An HTTP Request Parameter

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

1. An XSQL local page parameter

2. An XSQL Request parameter

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

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

This 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

• JDK 1.3

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

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

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

• Oracle Internet Application Server 8i

• Allaire JRun 2.3.3

• Apache 1.3.9 with JServ 1.0 and 1.1

• Apache 1.3.9 with Tomcat 3.1 Servlet Engine

• Apache Tomcat 3.1 Web Server + Servlet Engine

• Caucho Resin 1.1

• Java Web Server 2.0

• Weblogic 5.1 Web Server

• NewAtlanta ServletExec 2.2 for IIS/PWS 4.0

• Oracle8i Lite Web-to-Go Server

• Oracle8i 8.1.7 Oracle Servlet Engine

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

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

• Apache 1.3.9 with Tomcat 3.1 Servlet Engine

• Apache Tomcat 3.1 Web Server + Tomcat 3.1 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 Oracle Servlet Engine

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

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

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

• Oracle8i 8.1.5 Driver for JDBC 1.x

• Oracle8i 8.1.6 Driver for JDBC 1.x

• Oracle8i 8.1.7 Driver for JDBC 1.x

• Oracle8i Lite 4.0 Driver for JDBC 1.x

• Oracle8i 8.1.6 Driver for JDBC 2.0

• Oracle8i 8.1.7 Driver for JDBC 2.0

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 1.0.2 depends on:

• Oracle XML Parser V2, version 2.0.2.9 or higher

• Oracle XML SQL Utilities for Java, version 2.0.1 or higher

• A Web Server that supports Java Servlets

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

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

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_2_0.tar.gz or xsqlservlet_v1_0_2_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_2_0.tar.gz

   on Unix, or on Windows:

   pkzip25 -extract -directories xsqlservlet_v1_0_2_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:

   • sax2.jar - SAX2 archive

   • xsu12.jar- Oracle XML SQL Utility

   • xmlparserv2.jar- Oracle XML Parser for Java V2

   • oraclexsql.jar- Oracle XSQL Pages

   • classes12.jar- Oracle JDBC Driver

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

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

   
   

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

   
   

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

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

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

1. Setup the Server CLASSPATH Correctly for the XSQL Servlet

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

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

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

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

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

     FOPHOME/fop_bin_0_14_0.jar;FOPHOME/lib/w3c.jar

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

     C:\xsql\lib\sax2.jar;
     C:\xsql\lib\xsu12.jar;
     C:\xsql\lib\xmlparserv2.jar;
     C:\xsql\lib\classes12.zip;
     C:\xsql\lib\oraclexsql.jar;
     C:\xsql\lib

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

   FOPHOME/fop_bin_0_14_0.jar;FOPHOME/lib/w3c.jar

2. Map the .xsql file extension to the XSQL Servlet

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

   Then click the Service Config button...

   On the Service Config screen, select the Mappings tab.

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

3. Map an /xsql/ virtual directory

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

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

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

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

Restart the JRun server and browse the URL:

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

1. Setup the Server CLASSPATH Correctly for the XSQL Servlet

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

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

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

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

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

2. Map the .xsql file extension to the XSQL Servlet

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

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

3. Map an /xsql/ virtual directory

   In this step, we want to map the virtual path /xsql/ to C:\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:

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

     rem Set up the CLASSPATH that we need
     
     set cp=%CLASSPATH%
     
     set CLASSPATH=.
     set CLASSPATH=%TOMCAT_HOME%\classes
     set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\webserver.jar
     set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\jasper.jar
     set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\xml.jar
     set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\servlet.jar
     set CLASSPATH=%CLASSPATH%;%JAVA_HOME%\lib\tools.jar
     
     REM Added for Oracle XSQL Servlet
     REM -----------------------------
     set CLASSPATH=%CLASSPATH%;C:\xsql\lib\sax2.jar
     set CLASSPATH=%CLASSPATH%;C:\xsql\lib\xsu111.jar
     set CLASSPATH=%CLASSPATH%;C:\xsql\lib\oraclexsql.jar
     set CLASSPATH=%CLASSPATH%;C:\xsql\lib\xmlparserv2.jar
     set CLASSPATH=%CLASSPATH%;C:\xsql\lib\classes111.zip
     set CLASSPATH=%CLASSPATH%;C:\xsql\lib
     REM FOR Apache FOP Generation, Add
     REM set CLASSPATH=%CLASSPATH%;FOPHOME/fop_bin_0_14_0.jar
     REM set CLASSPATH=%CLASSPATH%;FOPHOME/lib/w3c.jar

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

     rem Set up the CLASSPATH that we need
     
     set cp=%CLASSPATH%
     
     set CLASSPATH=.
     set CLASSPATH=%TOMCAT_HOME%\classes
     set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\webserver.jar
     set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\jasper.jar
     set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\xml.jar
     set CLASSPATH=%CLASSPATH%;%TOMCAT_HOME%\lib\servlet.jar
     set CLASSPATH=%CLASSPATH%;%JAVA_HOME%\lib\tools.jar
     
     REM Added for Oracle XSQL Servlet
     REM -----------------------------
     set CLASSPATH=%CLASSPATH%;C:\xsql\lib\sax2.jar
     set CLASSPATH=%CLASSPATH%;C:\xsql\lib\xsu12.jar
     set CLASSPATH=%CLASSPATH%;C:\xsql\lib\oraclexsql.jar
     set CLASSPATH=%CLASSPATH%;C:\xsql\lib\xmlparserv2.jar
     set CLASSPATH=%CLASSPATH%;C:\xsql\lib\classes12.zip
     set CLASSPATH=%CLASSPATH%;C:\xsql\lib
     REM FOR Apache FOP Generation, Add
     REM set CLASSPATH=%CLASSPATH%;FOPHOME/fop_bin_0_14_0.jar
     REM set CLASSPATH=%CLASSPATH%;FOPHOME/lib/w3c.jar

2. Map the .xsql file extension to the XSQL Servlet

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

   By default, Tomcat 3.1 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"...

   <Context path="/test" docBase="webapps/test" debug="0" reloadable="true" /> 
   
   <!--
    |  Define a Servlet context for the XSQL Servlet  
    |  
    |  The XSQL Servlet ships with a .\WEB-INF directory
    |  with its web.xml file pre-configured for C:\xsql
    |  installation.
    +-->
   <Context path="/xsql" docBase="C:\xsql"/>
   

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

   
   

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

   
   

3. Map an /xsql/ virtual directory

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

Restart the Tomcat server and browse the URL:

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

1. Setup the Server CLASSPATH Correctly for the XSQL Servlet

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

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

2. Map the .xsql file extension to the XSQL Servlet

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

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

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

3. Map an /xsql/ virtual directory

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

Restart the IIS server and browse the URL:

http://localhost/xsql/index.html

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

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

• Insure that the XML SQL Utility is loaded into the database with appropriate public synonyms so the XSQL schema can resolve its classes. By default, the 8.1.7 release ships with a version of the catxsu.sql script which does not correctly create these public synonyms. To remedy the problem:

  1. Edit ORACLEHOME/rdbms/admin/catxsu.sql

  2. Find the line:

     call sys.dbms_java.loadjava('-v -r -grant PUBLIC rdbms/jlib/xsu12.jar');

  3. Change it to read:

     call sys.dbms_java.loadjava('-v -r -grant PUBLIC -synonym rdbms/jlib/xsu12.jar');

  4. Rerun this edited catxsu.sql script as SYS

• Create a database user named XSQL to own the XSQL Servlet installation.

  1. sqlplus SYS/password

  2. grant connect, resource, javauserpriv, javasyspriv to XSQL;

• Load the contents of oraclexsql.jar into the XSQL schema.

  loadjava -user XSQL/XSQL -verbose -resolve oraclexsql.jar

  
  

  Note: Resolution errors relating to "FOP" and to "XMLNodeUtil" are harmless and can be ignored.

  
  

• Create a new web service with a single endpoint and make the XSQL user the owner

  1. Launch the session shell utility, connecting as SYS:

     sess_sh -user SYS/password -service sess_iiop://your817server:2481:ORCL

  2. Destroy any existing service named XSQLWebService

     destroyservice -all XSQLWebService

  3. Create a service named XSQLWebService, rooted at the /XSQLWebService namespace:

     createwebservice -root /XSQLWebService XSQLWebService

  4. Change the owner of /XSQLWebService to XSQL:

     chown XSQL /XSQLWebService

  5. Recursively give read, write, execute privs to every object in the /XSQLWebService to XSQL:

     chmod -R +rwx XSQL /XSQLWebService

  6. Add an endpoint for the service to accept HTTP requests on any convenient port (e.g. 8182):

     addendpoint -port 8182 XSQLWebService XSQLWebService8182

  7. Exit the session shell utility (logged in as SYS).

     exit

• As XSQL, use the session shell utility to create a web domain and publish the XSQL Servlet into it:

  1. Launch the session shell utility, connecting as XSQL:

     sess_sh -user XSQL/XSQL -service sess_iiop://your817server:2481:ORCL

  2. Create a web domain, mapping its virtual root directory to a convenient physical file system directory:

     createwebdomain -docroot F:\oracle\htdocs /XSQLWebService

  3. Publish the XSQL Servlet into the default context of this new domain. The options below should be entered all on a single line when typed in:

     publishservlet -virtualpath *.xsql
                    -stateless
                    /XSQLWebService/contexts/default
                    xsql 
                    XSQL:oracle.xml.xsql.XSQLServlet

• To setup an MTS-based HTTP listener running inside the database, add the following line to your database's init.ora file:

  mts_dispatchers = 
     "(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8182))(PRE=http://XSQLWebService)"

• Stop and restart your database

• Edit the default XSQLConfig.xml that ships with XSQL Servlet to make the following changes:

  1. Use the KPRB driver connection string in the <dburl>

          <connection name="demo">
            <username>scott</username>
            <password>tiger</password>
            <dburl>jdbc:oracle:kprb:@</dburl> <!-- CHANGE THIS LINE -->
            <driver>oracle.jdbc.driver.OracleDriver</driver>
          </connection>

  2. Disable connection pooling by editing the <initial> and <increment> values as shown below:

          <connection-pool>
            <initial>1</initial>
            <increment>0</increment>
            <timeout-seconds>60</timeout-seconds>
            <dump-allowed>no</dump-allowed>
          </connection-pool>

• Load the XSQLConfig.xml file into the database as a Java resource:

  loadjava -user XSQL/XSQL -verbose XSQLConfig.xml

• Copy any XSQL pages that you want to run into the physical directory that you mapped as the virtual root of the XSQLWebService above, then request them by URL from the web server running inside the database like:

  http://your817server:8182/foo.xsql

  or

  http://your817server:8182/any/subdirectory/foo.xsql

To setup the data for the demos do the following:

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

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

   GRANT EXECUTE ON CTX_DDL TO SCOTT;

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

   GRANT QUERY REWRITE TO SCOTT;

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

4. Connect to your database as SCOTT/TIGER.

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

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

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

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 SQL, XML, and XSLT.

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

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