XSQL Servlet Features

XSQL Servlet uses XML Parser for JavaV2 and XML- SQL Utility to generate XML pages for SQL queries. It can combine the power of SQL, XML, and XSLT in the server with HTTP protocol as a transport mechanism, to do the following tasks:

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

• Query a logical "view" of business data requested anywhere

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

• Transform the information into any XML, HTML, or text format required

XSQL Pages bring this capability to you by automating the use of underlying XML components to solve common cases with no programming involved.

See Also: Appendix B, "XDK for Java: Specifications and Cheat Sheets" for the XSQL Servlet specifications and cheat sheets, and the XSQL Servlet Release Notes on OTN at http://technet.oracle.com/tech/xml

Introducing Oracle XSQL Pages

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

• Assemble dynamic XML "data pages" based on one or more parameterized SQL query

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

For example, to serve a real-time XML "datagram" from Oracle8i of all available flights landing today at JFK airport just build an XSQL Page named AvailableFlightsToday.xsql that looks like the example below. Use your favorite text editor...

<?xml version="1.0"?>
<xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
    SELECT Carrier,
           FlightNumber,
           Origin,
           TO_CHAR(ExpectedTime,'HH24:MI')
           AS Due
           FROM FlightSchedule
     WHERE TRUNC(ExpectedTime) = TRUNC(SYSDATE)
       AND Arrived = 'N'
       AND Destination = '{@City}'
  ORDER BY ExpectedTime
</xsql:query>

Save the file, and then browse the URL:

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

By installing the XSQL Servlet on your favorite web server, and copying the AvailableFlightsToday.xsql file to that server's virtual root directory, you are already in business.

The results of the query in your XSQL Page are materialized automatically as XML and returned to the requestor. This XML-based "datagram" would typically be requested by another server program for processing, but if you are using a browser such as Internet Explorer 5.0, you can directly view the XML result as shown in Figure 19-1.

Figure 19-1 XML Result From XSQL Page (AvailableFlightsToday.xsq) Query

If the canonical <ROWSET> and <ROW> XML output from Figure 19-1 is not the XML format that the requestor wants, then use an XSLT stylesheet to transform this XML document into the XML document format desired.

Typically the desired format is communicated to you to by a Document Type Descriptor (DTD). A DTD is in effect, a "schema" definition. It formally defines what XML content the documents of that type can have.

Assume you are given the flight-list.dtd definition and are told to produce your list of arriving flights in a format compliant with that DTD. You can use a visual tool such as Extensibility's "XML Authority" to browse the structure of the flight-list DTD as shown in Figure 2.

Exploring the "industry-standard" flight-list.dtd using Extensibility's XML Authority 1.1

Figure 19-2 Exploring the 'industry standard' flight-list.dtd using Extensibility's XML Authority

This shows that the standard XML formats for Flight Lists are:

• <flight-list> element, containing one or more...

• <flight> elements, having attributes airline and number, each of which contains an...

• <arrives> element.

By associating the following XSLT stylesheet, flight-list.xsl, with the XSQL Page, you can "morph" the default <ROWSET> and <ROW> format of your arriving flights query results into the "industry standard" DTD format.

<!-- XSLT Stylesheet to transform ROWSET/ROW results into flight-list format --> 
<flight-list xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
xsl:version="1.0">   
  <xsl:for-each select="ROWSET/ROW">
      <flight airline="{CARRIER}" number="{FLIGHTNUMBER}">         
        <arrives><xsl:value-of select="DUE"/></arrives>
      </flight>
  </xsl:for-each>
</flight-list>

The stylesheet is a template that includes the literal elements that you want produced in the resulting document, such as, <flight-list>, <flight>, and <arrives>, interspersed with special XSLT "action elements" that allow you to do the following:

• Loop over matching elements in the source document using <xsl:for-each>

• Plug in the values of source document elements where necessary using <xsl:value-of>

• Plug in the values of source document elements into attribute values using {something}

Note two things have been added to the top-level <flight-list> element in the stylesheet:

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

  This defines the XML Namespace (xmlns) named "xsl" and identifies the uniform resource locator string that uniquely identifies the XSLT specification. Although it looks just like a URL, think of the string http://www.w3.org/1999/XSL/Transform as the "global primary key" for the set of elements that are defined in the XSLT 1.0 specification. Once the namespace is defined, we can then make use of the <xsl:XXX> action elements in our stylesheet to loop and plug values in where necessary.

• xsl:version="1.0"

  This attribute identifies the document as an XSLT 1.0 stylesheet. A version attribute is required on all XSLT Stylesheets for them to be valid and recognized by an XSLT Processor.

Associate the stylesheet to your XSQL Page by adding an <?xml-stylesheet?> processing instruction to the top of the page as follows:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="flight-list.xsl"?>
<xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
    SELECT Carrier,
           FlightNumber,
           Origin,
           TO_CHAR(ExpectedTime,'HH24:MI') AS Due
      FROM FlightSchedule
     WHERE TRUNC(ExpectedTime) = TRUNC(SYSDATE)
       AND Arrived ='N'
       AND Destination = '{@City}'
  ORDER BY ExpectedTime
</xsql:query>

This causes the requesting program or browser to see the XML in the "industry-standard" format as specified by flight-list.dtd you were given as shown in Figure 19-3.

Figure 19-3 XSQL Page Results in "Industry Standard" XML Format

To return the same XML information in HTML instead, simply use a different XSLT stylesheet.

Rather than producing elements like <flight-list> and <flight>, your stylesheet produces HTML elements like <table>, <tr>, and <td>.

The result of the dynamically queried information then looks like the HTML page shown in Figure 19-4. Instead of returning "raw" XML information, the XSQL Page leverages server-side XSLT Transformation to format the information as HTML for delivery to the browser.

Figure 19-4 Using an Associated XSLT Stylesheet to Render HTML

Similar to the syntax of the flight-list.xsl stylesheet, the flight-display.xsl stylesheet looks like a template HTML page, with <xsl:for-each>, <xsl:value-of> and attribute value templates like {DUE} to plug in the dynamic values from the underlying <ROWSET> and <ROW> structured XML query results.

<!-- XSLT Stylesheet to transform ROWSET/ROW results into HTML -->
<html xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xsl:version="1.0">
  <head>
    <style> td {font-family:verdana,arial; font-size:40;
            background-color:#f7f7e7; color:#000000 }
      th,table {font-family:verdana,arial; font-size:40;
            background-color:#cccc99; color:#336699 }
   </style> 
   </head>
   <body>
     <center>
       <table border="0">
         <tr>
           <th>Flight</th>
           <th>Arrives</th>
         </tr>
           <xsl:for-each select="ROWSET/ROW">
         <tr>
         <td>
       <table border="0" cellspacing="0" cellpadding="4">
         <tr>
         <td><img align="absmiddle" src="images/{CARRIER}.gif"/>
         </td>
            <td width="180">
              <xsl:value-of select="CARRIER"/>
              <xsl:text> </xsl:text>
              <xsl:value-of select="FLIGHTNUMBER"/>
           </td></tr></table>
           </td>
           <td align="center">
             <xsl:value-of select="DUE"/>
           </td>
         </tr>
       </xsl:for-each>
     </table>
   </center>
 </body>
</html>

Note: The stylesheet looks exactly like HTML, with one tiny difference. It is well-formed HTML. This means that each opening tag is properly closed (e.g. <td>...</td>) and that empty tags use the XML empty element syntax <br/> instead of just <br>.

You can see that by combining the power of:

• Parameterized SQL statements to select any information you need from our Oracle database,

• Industry-standard XML as a portable, interim data exchange format

• XSLT to transform XML-based "data pages" into any XML- or HTML-based format you need

You can achieve very interesting and useful results quickly.

For more information on XSQL Pages refer to the XDK for Java, XSQL Servlet Release Notes that accompany the software download from the Oracle Technology Network at http://technet.oracle.com/tech/xml