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
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 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
These release notes explain in detail how to setup the Oracle XSQL Servlet and how to make use of all the time-saving features supported by XSQL Page templates.
Using this technology preview, the information you already have in your Oracle database, and the power of SQL, XML, and XSLT, you'll quickly discover that a powerful and flexible world of web data publishing is at your fingertips.
The new features and enhancements in this release are based squarely on an incredible amount of user-feedback from our Oracle Technet XML Discussion Forum, where XSQL Page users post questions and feature ideas. We hope you like what's in this release since, in the best possible interpretation of the phrase, "You asked for it!"
Get Started Quickly with XSQL Servlet on Windows with Web-to-Go Web Server
This release includes a working version of the single-user web server that is part of the Web-to-Go mobile application platform along with the
New Online Help System
This release includes a new, on-line Help System (built using XSQL Pages, of course) giving helpful information about the syntax and options for each XSQL Action Element. In additional helpful examples are provided illustrating each action. To access the help system, after successfully installing the XSQL Servlet, browse the URL:
http://yourmachine/xsql/index.html
New Built-in XSQL Action Elements
A number of new built-in XSQL Action Elements have been added in this release. The following table documents the new arrivals...
Action Element | Description |
|
Includes the canonical XML representation of the result set of a cursor returned by a PL/SQL stored function. Using the new dynamic SQL features that are built-in to PL/SQL in Oracle8i, this means that |
|
Set a page-level (local) parameter that can be referred to in subsequent SQL statements in the page. The value can be set using a static value, the value of another parameter, or the results of a SQL statement. |
|
Include a parameter and its value as an element in your XSQL page. |
|
Set an HTTP-Session level parameter. The value can be set using a static value, the value of another parameter, or the results of a SQL statement. |
|
Set an HTTP Cookie. The value can be set using a static value, the value of another parameter, or the results of a SQL statement. |
|
Inserts the value of a single parameter containing XML. Can optionally supply a transform to get it in canonical format. |
In addition, numerous existing features have been enhanced and made more performant. Read on...
You can now use parameters to provide the page-level connection name. So, you can now say:
<MyPage xmlns:xsql="urn:oracle-xsql" connection="{@conn}">
to allow the name of the connection to be provided in the request.
You can now specify a
You can now specify a
You can now specify a
The XSQLCommandLine utility can now post XML to the XSQL page it is processing (typically, for inserting into the database) by specifying the filename or URL of the XML to be posted as the value of the posted-xml command line parameter. For example,
Custom action handlers may now refer to the content of the posted XML document any number of times necessary when calling
The
A Unix shell script
The
The XSQL named parameter mechanism has changed slightly to integrate new support for referencing the values of HTTP Cookies. XSQL provides a single way to refer to values that can be specified as:
HTTP Request Parameters
HTTP Cookies
HTTP Session Variables
Local XSQL Page Parameters
When you reference an parameter like
<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
An XSQL local page parameter
An HTTP Cookie
An HTTP Session Variable
An HTTP Request Parameter
If the request is being processed by a non-Servlet request method using
An XSQL local page parameter
An XSQL Request parameter
Provided on the command-line or passed into the
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
The
The
<XSQLConfig> : <connectiondefs dumpallowed="yes"> : </connectiondefs> </XSQLConfig>
Now you specify:
<XSQLConfig> : <processor> : <connection-pool> : <dump-allowed>yes</dump-allowed> </connection-pool> : </processor> : </XSQLConfig>
A number of new configuration options are now available in the
The default number of records to fetch per database round-trip for
The size (in pages) of the XSQL Page LRU cache.
The size (in stylesheets) of the XSLT Stylesheet LRU cache.
The size (in bytes) of the Buffered Output Stream to use for XSQL Servlet (or zero to disable)
The initial and increment size of the connection pool, as well as the timeout threshold (in seconds) for cleaning up connections.
The initial and increment size of the XSLT Stylesheet pool, as well as the timeout threshold (in seconds) for cleaning up stylesheet instances.
The MIME types for which to suppress setting character-set information.
Whether or not to reload named connection definitions when an unknown connection name is requested.
Whether or not to include per-page and/or per-action-element timing information in the XSQL Page output.
For more information on these settings, see the in-line comments in the
Code optimizations have been implemented throughout.
A new XSLT Stylesheet pooling mechanism offers significant improvement in performance and throughput on multithreaded Servlet engines.
New LRU Caching algorithm (with tunable cache size) gives you better control over the memory used by XSQL Page processing.
When using the Oracle JDBC driver, the XSQL page processor takes advantage of bulk record fetching to reduce network round-trips between web server and database. A default fetch size can be specified in XSQLConfig.xml, and any xsql:query action element can provide an overriding fetch size using the new
Performance of
Demo files have been organized into individual subdirectories under
A new "SVG Demo" has been added that demonstrates a simple example of using XSQL Pages to query database information and transform it using XSLT into Scalable Vector Graphics.
Note
: To properly experience the Scalable Vector Graphics demonstration, you need to install an SVG plugin into your browser like the Adobe SVG Plugin. |
This technical preview release contains the following known issues:
HTTP parameters with multibyte names (e.g. a parameter whose name is in Kanji) are properly handled when they are inserted into your XSQL page using
Using the
The XSQL Servlet is designed to run on any Java VM, using any JDBC driver, against any database. In practice, we are able to test it against only the most popular configurations of these. In this section we document the supported configurations that have been tested in the Oracle labs.
The XSQL Pages and XSQL Servlet have been tested using:
JDK 1.1.8
JDK 1.2.2
These are the only two JDK versions that we know work correctly.
Note
: Numerous users have reported problems using XSQL Pages and the XSQL Servlet with JDK 1.1.7 which suffers problems in its character set conversion routines for UTF-8 that make it unusable for processing XSQL Pages. |
This XSQL Servlet has been tested with the following servlet engines:
Allaire JRun 2.3.3
Apache 1.3.9 with JServ 1.0 and 1.1
Apache 1.3.9 with Tomcat 3.1 Beta1 Servlet Engine
Apache Tomcat 3.1 Beta1 Web Server + Servlet Engine
Caucho Resin 1.1
NewAtlanta ServletExec 2.2 for IIS/PWS 4.0
Oracle8i Lite Web-to-Go Server
Oracle Application Server 4.0.8.1 (with "JSP Patch")
Oracle8i 8.1.7 Beta "Aurora" Servlet Engine
Sun JavaServer Web Development Kit (JSWDK) 1.0.1 Web Server
JavaServer Pages can use
Apache 1.3.9 with Tomcat 3.1 Beta1 Servlet Engine
Apache Tomcat 3.1 Beta1 Web Server + Tomcat 3.1 Beta1 Servlet Engine
Caucho Resin 1.1 (Built-in JSP 1.0 Support)
NewAtlanta ServletExec 2.2 for IIS/PWS 4.0 (Built-in JSP 1.0 Support)
Oracle8i Lite Web-to-Go Server with Oracle JSP 1.0
Oracle8i 8.1.7 Beta "Aurora" Servlet Engine with Oracle JSP 1.0
Any Servlet Engine with Servlet API 2.1+ and Oracle JSP 1.0
In general, it should work with any servlet engine supporting the Servlet 2.1 Specification or higher, and the Oracle JSP 1.0 reference implementation or functional equivalent from another vendor.
The Oracle XSQL Page processor has been designed to exploit the maximum set of features against the Oracle JDBC drivers, but gracefully degrade to work against any database with a reasonable JDBC driver. While numerous users have reported successfully using XSQL Pages with many other JDBC drivers, the ones that we have tested in-house are:
Oracle8i 8.1.5 Driver for JDBC 1.x
Oracle8i 8.1.6 Driver for JDBC 1.x
Oracle8i Lite 4.0 Driver for JDBC 1.x
Oracle8i 8.1.6 Driver for JDBC 2.0
Note
: At this time, the
|
Note
: If you are using the Oracle 8i 8.1.6 Driver for JDBC 2.0 (in classes12.zip) you must download and use the corresponding Oracle XML SQL Utility for JDBC 2.0 (in XSU12.zip) from Oracle Technology Network. |
Oracle XSQL Pages depends on:
Oracle XML Parser V2 Release 2.0.2.7
Oracle XML SQL Utilities for Java
A Web Server that supports Java Servlets
A JDBC driver, like Oracle JDBC or Oracle8i Lite JDBC
For your convenience, all of these dependent libraries are included with the XSQL Servlet distribution when you download it.
You can download the XSQL Servlet distribution by:
Clicking on the 'Software' icon at the top of the page:
Logging in with your OTN username and password (registration is free if you do not already have an account).
Selecting whether you want the NT or Unix download (both contain the same files)
Acknowledging the licensing agreement and download survey
Clicking on
To extract the contents of the XSQL Servlet distribution, do the following:
Choose a directory under which you would
like the
Change directory to
tar xvfz xsqlservlet_v1_0_0_0.tar.gz
on Unix, or on Windows:
pkzip25 -extract -directories xsqlservlet_v1_0_0_0.zip
using the pkzip25 command-line tool or the WinZip visual archive extraction tool.
The XSQL Servlet comes bundled with the Oracle Web-to-go server that is pre-configured to use XSQL Pages. The Web-to-go web server is a single-user server, supporting the Servlet 2.1 API, used for mobile application deployment and for development. This is a great way to try XSQL Pages out on your Windows machine before delving into the details of configuring another Servlet Engine to run XSQL Pages.
Note
: The Web-to-go Web server is part of Oracle's development and deployment platform for mobile applications.For more information on Web-to-go, please visit http://www.oracle.com/mobile |
Windows users can get started quickly with XSQL Pages by:
Running the
Browsing the URL
If you get an error starting this script, edit the
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
[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
The demos are set up to use the
<?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:
Include the list of XSQL Java archives as well as the directory where
Note
:
For convenience, the |
Map the
Map a virtual directory
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:\xsql\lib\xmlparserv2.jar; C:\xsql\lib\oraclexmlsql.jar; C:\xsql\lib\classes111.zip; C:\xsql\lib\oraclexsql.jar; C:\xsql\lib
Map the
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
Map an
In this step, we want to map the virtual path
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
Restart the JRun server and browse the URL:
http://localhost:8000/xsql/index.html
Setup the Server CLASSPATH Correctly for the XSQL Servlet
This is done by editing the JServ configuration file named
# Oracle XSQL Servlet wrapper.classpath=C:\xsql\lib\oraclexsql.jar # Oracle JDBC (8.1.6) wrapper.classpath=C:\xsql\lib\classes111.zip # Oracle XML Parser V2 (with XSLT Engine) wrapper.classpath=C:\xsql\lib\xmlparserv2.jar # Oracle XML SQL Components for Java wrapper.classpath=C:\xsql\lib\oraclexmlsql.jar # XSQLConfig.xml File location wrapper.classpath=C:\xsql\lib
Map the
To do this, you need to edit the JServ configuration file named
# Executes a servlet passing filename with proper extension in PATH_TRANSLATED # property of servlet request. # Syntax: ApJServAction [extension] [servlet-uri] # Defaults: NONE ApJServAction .xsql /servlets/oracle.xml.xsql.XSQLServlet
Map an
In this step, we want to map the virtual path
Alias /xsql/ "C:/xsql/"
Restart the Apache server and browse the URL:
http://localhost/xsql/index.html
Setup the Server CLASSPATH Correctly for the XSQL Servlet
This is done by editing the Tomcat startup script named
Map the
Tomcat supports creating any number of configuration "contexts" to better organize the web applications your site needs to support. Each context is mapped to a virtual directory path, and has its own separate servlet configuration information. XSQL Servlet comes with a pre-configured context file to make XSQL Servlet setup easier.
By default, Tomcat 3.1 Beta 1 comes pre-configured with the following contexts (defined by
The root context
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
Note that the
Note
: To add the XSQL Servlet to an existing context, add the servlet and servlet-mapping entries that you find in |
Map an
This is already achieved by creating the
Restart the Tomcat server and browse the URL:
http://localhost:8080/xsql/index.html
Setup the Server CLASSPATH Correctly for the XSQL Servlet
This is done by browsing the url http://localhost/servlet/admin after starting the IIS Server, and clicking the VM Settings link under "Advanced" in the sidebar.
Add the four archives and one directory as shown above, by adding them one at a time and clicking the Submit button after each new entry.
Map the
Click on Configure under the "Servlets" heading in the sidebar to browse the form where you register servlets. Enter a Servlet Name of
Then, click on Aliases under "Servlets" in the sidebar.
Add an entry as shown below mapping
Map an
Use the IIS Admin console to create an
Restart the IIS server and browse the URL:
http://localhost/xsql/index.html
To setup the data for the demos do the following:
Change directory to the
In this directory, run SQLPLUS. Connect to your database as
GRANT EXECUTE ON CTX_DDL TO SCOTT;
Connect to your database as
GRANT QUERY REWRITE TO SCOTT;
This allows SCOTT to create a functional index that one of the demos uses to perform case-insensitive queries on descriptions of airports.
Connect to your database as
Run the script
Change directory to the
To use Oracle XSQL Pages just include a
The XSQL Page Processor expects to find an attribute named
The simplest usage of the
<?xml version="1.0"?> <xsql:query xmlns:xsql="urn:oracle-xsql" connection="demo"> SELECT 'Hello World' AS "GREETING" FROM DUAL </xsql:query>which produces the resulting dynamically created XML document:
<?xml version = '1.0'?> <ROWSET> <ROW id="1"> <GREETING>Hello World</GREETING> </ROW> </ROWSET>An
Note
:
To have the dynamic XML-results of your
|
For example, you could build up a "data page" out of two queries like this:
<?xml version="1.0"?> <sales-by-year xmlns:xsql="urn:oracle-xsql" connection="salesdb"> <period id="H1" year="CY99"> <xsql:query> SELECT salesperson, SUM(sales) AS Total FROM sales WHERE sale_date between '01-JAN-99' and '30-JUN-99' GROUP BY salesperson </xsql:query> </period> <period id="H2" year="CY99"> <xsql:query> SELECT salesperson, SUM(sales) AS Total FROM sales WHERE sale_date between '01-JUL-99' and '31-DEC-99' GROUP BY salesperson </xsql:query> </period> </sales-by-year>which would produce results like:
<?xml version="1.0"?> <sales-by-year connection="salesdb"> <period id="H1" year="CY99"> <ROWSET> <ROW id="1"> <SALESPERSON>Steve</SALESPERSON> <TOTAL>23465500</TOTAL> </ROW> <ROW id="2"> <SALESPERSON>Mark</SALESPERSON> <TOTAL>39983400</TOTAL> </ROW> </ROWSET> </period> <period id="H2" year="CY99"> <ROWSET> <ROW id="1"> <SALESPERSON>Steve</SALESPERSON> <TOTAL>67788400</TOTAL> </ROW> <ROW id="2"> <SALESPERSON>Mark</SALESPERSON> <TOTAL>55786990</TOTAL> </ROW> </ROWSET> </period> </sales-by-year>
While the two examples above show the default behavior for
SQL-to-XML query results, you can customize many of the aspects of
the XML query results produced for each
The following attribute-based options correspond one-to-one to
features offered by the underlying
The XSQL Page Processor supports the following (case-sensitive) attributes on
the
|
|
Attribute Name | Description |
|
Element name to use for the query results. Set equal to
the empty string to suppress printing a document
element. (Default is |
|
Element name to use for each row in the query results. Set
equal to the empty string to supress printing a row
element. (Default is |
|
Maximum number of rows to fetch from the query. Useful for
fetching the "top-N" or, in combination with
|
|
Number of rows to skip over before returning the query results. (Default is not skip any rows) |
|
Attribute name for the id attribute for each row in the query result. (Default is |
|
Column name to use to supply the value of the id attribute for each row in the query result. (Default is to use the row count as the id attribute value) |
|
If set to |
|
If set to |
The SQL statement provided in the content of the
<?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
http://yourserver/xsql/demo/airport.xsql?airport=SQLThe example above would return the following XML document (Note the use of custom
<?xml version = '1.0'?> <Ok> <Airport id="1"> <programlisting>SQL</programlisting> <Description>San Carlos, California, Usa</Description> </Airport> </Ok>
Nested inside a
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
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
Given the XML output of an
<?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
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
on the document element of the target page before making use of the special
In both transformation examples above, notice that the value of the
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
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:
can be used nested inside of an:
element to refer relatively to the
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
http://yourmachine/xmlinout/emp.xsql?id=7499
produces the HTML page:
<html> <head> <META http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Employee Info</title> </head> <body> <table border="1" cellspacing="0"> <tr> <th>Employee Name</th> <th>Salary</th> </tr> <tr> <td>ALLEN</td> <td>1600</td> </tr> </table> </body> </html>
So by writing an XSQL page with some queries and associatng an XSL Transform to it, you can publish any data in your database into any desired XML, HTML, or Textual format that you need over the web without writing code.
See the demos that accompany the XSQL Servlet release for lots of examples of combining
As we've seen, if you include an
<?xml 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
Multiple
For example, the following processing instructions at the top of an
<?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
One additional pseudo-attributes can be supplied on the
|
|
Attribute Name | Description |
|
Must be present and be set to the value |
|
Must be present. It indicates the relative or absolute URL to the stylesheet to be used. |
|
This value is optional. If set, this attribute's value is used to perform a case-insensitive match on the User-Agent string of the requesting browser so that an appropriate stylesheet can be used depending on the requesting software/device. |
|
If set to " |
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
If the browser is Internet Explorer 5.0, use
Otherwise, use
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
http://yourserver/yourdatapage.xsql?param1=value&xml-stylesheet=yourstyle.xsl
will cause the XSQL Page Processor to add the
If you want to force the
http://yourserver/yourdatapage.xsql?param1=value&xml-stylesheet=none
This can be useful during development.
You can supply the additional
Note
:
Any XSQL page can prevent externally-supplied stylesheets from being
considered by the XSQL Page Processor by including the XML attribute
|
Note
:
The XSQL Page Processor performs the same stylesheet processing mechanics
regardless of whether your |
The XSQL Pages we've seen so far all show examples of using the
Structured columns can be one of three types:
Strongly Typed, User-Defined Object
Strongly Typed, User-Defined Collection
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
CREATE TYPE POINT AS OBJECT (X NUMBER, Y NUMBER);
and have used your new
CREATE TABLE LOCATION ( NAME VARCHAR2(80), ORIGIN POINT );
and have inserted a row into this
INSERT INTO LOCATION VALUES ( 'Someplace', POINT(11,17) ); COMMIT;
Then, an XSQL page 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
Note
: See the supplied |
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
Taking the familar
<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
Since the
One or more
One or more
The SQL statement that may appear within the
Any SQL statement can be included in an
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
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
The
The fact that
An arbitrary XML document
A database table
You can create an XSL Transformation that transforms the source document
Let's say that the source document is an XML news feed like what you'll see if you browse the following URL from www.moreover.com ...
http://www.moreover.com/cgi-local/page?index_xml+xml
A shortened version of such a resulting XML document looks like this:
<?xml version="1.0"?> <!DOCTYPE moreovernews SYSTEM "http://www.moreover.com/xml/moreovernews.dtd"> <moreovernews> <article id="4227581"> <url>http://d.moreover.com/click/here.pl?x4227575</url> <headline_text>Austin: webMethods gets deal with Dell</headline_text> <source>dbusiness.com</source> <media_type>text</media_type> <cluster>XML and metadata news</cluster> <tagline>Austin</tagline> <document_url>http://washington.dbusiness.com/</document_url> <harvest_time>Oct 30 1999 7:08AM</harvest_time> <access_registration> </access_registration> <access_status> </access_status> </article> <article id="4212701"> <url>http://d.moreover.com/click/here.pl?x4212698</url> <headline_text>Microsoft continues XML push with resource kit</headline_text> <source>InfoWorld</source> <media_type>text</media_type> <cluster>XML and metadata news</cluster> <tagline></tagline> <document_url>http://www.infoworld.com/</document_url> <harvest_time>Oct 29 1999 7:27AM</harvest_time> <access_registration> </access_registration> <access_status> </access_status> </article> <article id="4202251"> <url>http://d.moreover.com/click/here.pl?x4202247</url> <headline_text>IBM Brings XML To MQSeries</headline_text> <source>Internet Week</source> <media_type>text</media_type> <cluster>XML and metadata news</cluster> <tagline></tagline> <document_url>http://www.internetwk.com/</document_url> <harvest_time>Oct 28 1999 4:28PM</harvest_time> <access_registration> </access_registration> <access_status> </access_status> </article> <article id="4082434"> <url>http://d.moreover.com/click/here.pl?x4082432</url> <headline_text>XML leader OnDisplay's travel clients praised</headline_text> <source>Web Travel News</source> <media_type>text</media_type> <cluster>XML and metadata news</cluster> <tagline></tagline> <document_url>http://www.webtravelnews.com/</document_url> <harvest_time>Oct 20 1999 7:34AM</harvest_time> <access_registration> </access_registration> <access_status> </access_status> </article> </moreovernews>Next, let's say that you have a table called
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
We start by using an XSQL page to quickly produce one row of canonical query results from the
<?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
<?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 "
Add an
Remove the
Remove any elements corresponding to columns whose values will be assigned by database triggers, like the ID column will be in this example,
Surround the
Replace the literal text between the
The result of applying the changes outlined in the three steps above to the sample output of the:
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
<ROWSET xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
Added the
<xsl:for-each select="moreovernews/article">
so that for each
Removed the
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
Hard-coded the value of the
If we rename the now-edited
java oracle.xml.parser.v2.oraxsl moreover.xml moreover-to-newsstory.xsl out.xml
Note
: Since the <!DOCTYPE moreovernews SYSTEM "http://www.moreover.com/xml/moreovernews.dtd"> declaration with an java -DproxySet=true -DproxyHost=proxyServerName -DproxyPort=80 oracle.xml.parser.v2.oraxsl ... |
If we use Internet Explorer to browse the resulting
If we pass this resulting target document to the
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:
Choose the table or view you want to use for inserting the XML information,
Create an XSL Transformation that transforms the inbound document into the canonical format for this table or view,
We saw a "cookbook" approach above for how to do this easily based on a
Transform the inbound document into the canonical format for the table or view into which you want to insert it, and
Let
Oracle XSQL Pages support a simple
<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
<?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
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
Let's implement this behavior using a database
Since
CREATE VIEW newsstoryview AS SELECT * FROM newsstory
Then we can create the
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
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
<?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 |
The
<?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
When XML is posted to a web server through HTTP, it's ContentType is
<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
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:
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 table="newsstoryview" transform="request-to-newsstoryview.xsl"/
and by referencing the name of the
<?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
<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
Using the mechanism we learned in the previous section for associating XSL Stylesheets with XSQL Pages, we can include an
<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
<?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
...then rather than seeing the raw XML datagram returned by the
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
The |
You can process any XSQL page at the command line by using the
java oracle.xml.xsql.XSQLCommandLine xsqlURI [outputFile] [param1=value1 ... paramN=valueN]
The
xsql xsqlURI [outputFile] [param1=value1 ... paramN=valueN]
So, for example, to test the demo file
xsql airport.xsql airport=sfo xml-stylesheet=noneto output the results of processing the
xsql airport.xsql output.html airport=sfoto output the results to the
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
xsql insertnewsstory.xsql posted-xml=http://p.moreover.com/cgi-local/page?index_xml+xml
This will use the XSQL Page named
The following is a list of all built-in Action Elements that you can use in your XSQL Pages. ..
Action Element | Description |
|
Execute an arbitrary SQL statement and include its result set in canonical XML format. |
|
Execute a SQL DML statement or PL/SQL anonymous block. |
|
Set the value of a top-level XSLT stylesheet parameter. Value of the parameter can be set by supplying the optional "value" attribute, or by including a SQL statement as the element content. |
|
Insert the (optionally transformed) XML document that has been posted in the request into a database table or view. If HTML Form has been posted, then posted XML document is materialized from HTTP request parameters, cookies, and session variables. |
|
Include arbitrary XML resources at any point in your page by relative or absolute URL. |
|
Include key information like HTTP Parameters, Session Variable values and Cookies into your XSQL Page for addressing them in your stylesheet. |
|
Include the results of one XSQL Page at any point inside another. |
|
Include the results of executing a stored procedure that makes use of the Oracle Web Agent (OWA) packages inside the database to generate XML. |
|
Invoke a user-defined action handler, implemented in Java, for executing custom logic and including custom XML information into your XSQL Page. |
|
Includes the canonical XML representation of the result set of a cursor returned by a PL/SQL stored function. |
|
Set a page-level (local) parameter that can be referred to in subsequent SQL statements in the page. The value can be set using a static value, the value of another parameter, or the results of a SQL statement. |
|
Include a parameter and its value as an element in your XSQL page. |
|
Set an HTTP-Session level parameter. The value can be set using a static value, the value of another parameter, or the results of a SQL statement. |
|
Set an HTTP Cookie. The value can be set using a static value, the value of another parameter, or the results of a SQL statement. |
|
Inserts the value of a single parameter containing XML. Can optionally supply a transform to get it in canonical format. |
See the on-line help system for full details on each element. Additional notes on individual action elements follow below:
If you will be using the
If you run the XSQL Servlet from inside a corporate firewall, then you will need to uncomment the
The results of the XML resource included by an
The format of the XML document fragment that is included when you use
<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
The
<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
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:
Depending on the error the
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
JSP pages can use
Attributes of the
Client-overriding of stylesheets for an XSQL Page can be disallowed
with the new
The XSQL Page Processor loads the first
The
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
If you set the
<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
http://yoursever/your.xsql?dumppool=y
If you set the
<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
The XSQL Page Processor processes an XSQL Page by:
Looking for "Action Elements" from the
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:
Built-in XSQL Action Elements and Action Handler Classes
|
|
XSQL Action Element |
Handler Class in |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Action Handlers are initialized by getting passed the
To create a custom Action Handler, you need to provide a class that implements the
For convenience, you can save time by extending the base implementation class named
Note
: If you extend
// Call the superclass to intialize protected variables super(env,e); |
Illustrates an example of an action handler that replaces the action element with the current value of the database
getPageRequest().getJDBCConnection()
you can gain access to the current JDBC connection being used for this request. Note that the connection may be
Illustrates a handler that adds an element to the XSQL page with the value of the indicated parameter. Shows a custom
The following example shows a custom action handler
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
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
<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
<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
( |
Comments |
Hello World ( |
Simplest possible XSQL page. |
Do You XML Site ( |
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
|
Employee Page
( |
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 |
Insurance Claim Page ( |
Demonstrates a number of sample queries over the richly-structured, Insurance Claim object view.
The |
Invalid Classes Page
( |
XSQL Page which uses |
Airport Code Validation
( |
XSQL page which returns a "datagram" of
information about airports based on their three-letter
code. Demonstrates using the
The 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
( |
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 ( |
Demonstrates using an object view to group master/detail information from two existing "flat" tables like EMP and DEPT. The The |
Adhoc Query Visualization ( |
Demonstrates passing the entire SQL query and XSLT Stylesheet to use as parameters to the server. |
XML Document Demo ( |
Demonstrates inserting XML documents into relational tables. The Try inserting the text of the document in The 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 ( |
Demonstrates posting XML from a client to an XSQL Page that handles inserting the posted XML information into a database table using the
In this case, the program doing the posting of the XML is a client-side web page using Internet Explorer 5.0 and the Try copying and pasting the example
The |
SVG Demo ( |
The |
Once you've understood what the XSQL Page Processor running inside the XSQL Servlet does, you'll realize that Oracle XSQL Pages is really just a very thin layer of convenience functionality allowing you to leverage the tremendous flexibility and power of SQL, XML, and XSLT to really do the "heavy lifting".
The goal of this Oracle XSQL Pages Technology Preview is to simplify the task of experimenting with SQL/XML/XSL data publishing and provide us feedback on where you'd like the technology to go to make your Oracle data work harder for you on the Web.
Mail any feedback, comments, or suggestions to Steve Muench and I'll make sure it gets to the right development team within Oracle.
Note
: The explanatory and tutorial material included in this document is excerpted from my forthcoming book on Oracle and XML, due out sometime in the year 2000 from O'Reilly. Material has been included by permission. |