XML, XSLT, and Oracle8i How the Flight Finder Works July 2000

The figure below shows what the Flight Finder does.

1. Using any supported client device, an end-user fills out a form to specify a starting point and a destination. The form's source code specifies an XSQL page to execute when the end-user submits the form.
2. The Web server invokes the XSQL Servlet with an XSQL Page.
3. The XSQL Servlet parses the XSQL page and queries the database.
4. The database returns the query results, which the XSQL Servlet converts to an XML document.
5. The XSQL Servlet transforms the XML by applying an XSL stylesheet appropriate for the end-user's client device.
6. The Web server returns the customized document to the client.

With Oracle8i, you can run Oracle XML components and applications built with them inside the database. For devices and applications that require a smaller database footprint, you can use Oracle8i Lite to store and retrieve XML data. You can also run these components on a middle tier such as Oracle Internet Application Server 8i, or on the client.

Database to XML

This section describes how the Flight Finder queries the database and converts the result set to an XML document. The application is built from XSQL pages and XSL stylesheets: the XSQL pages define queries, and the XSL stylesheets format the query results. There's no Java code in the Flight Finder--it delegates processing chores to the Oracle XSQL Servlet.

The Flight Finder stores flight data in two tables, AIRPORTS and FLIGHTS. In AIRPORTS, the CODE column is the primary key. In FLIGHTS, the CODE column is the primary key, and the CODE_FROM and CODE_TO columns are foreign keys that reference AIRPORTS.CODE. The following SQL code shows the structures of these tables (column names in bold are primary keys, column names in italics are foreign keys).

create table airports
(
  code varchar2(3),
  name varchar2(64)
)

create table flights

(
  code varchar2(6),
  code_from varchar2(3),
  code_to varchar2(3),
  schedule date,
  status varchar2(1),
  gate varchar2(2)
)

Using the XSQL Servlet

The XSQL Servlet processes SQL queries and outputs the result set as XML. (The release notes for the latest version are here.) It is implemented as a Java servlet and takes as input an XSQL page (an XML file containing embedded SQL queries). It uses the XML Parser for Java and the XML SQL Utility for Java to perform many of its operations.

For example, the following code is from fly.xsql. It's XML with some special <xsql> tags for the XSQL Servlet to interpret. The flightFinderResult tag defines a structure that assigns values to parameters in a query. The tag also identifies a namespace for defining the xsql keyword and tells the XSQL servlet to use the (predefined) database connection named fly. The code uses the <xsql:query> tag to define a query (the XSQL Servlet download includes a Help System that describes the syntax and options for each XSQL tag). The code uses two other parameters (FROM and TO) in the body of the query statement to store the names of cities chosen by the end-user. (XSQL pages use the XSLT syntax {@param} to indicate a parameter.)

<?xml version="1.0"?>
...
<flightFinderResult xmlns:xsql="urn:oracle-xsql" connection="fly" lang="english">
  <xsql:set-stylesheet-param name="lang" value="{@lang}"/>
  <xsql:query tag-case="upper">
         <![CDATA[
         select F.code, F.code_from, A1.name as "depart_airport", 
                F.code_to, To_char(F.schedule, 'HH24:MI') as "Sched", 
                A2.name as "arrive_airport", 

                Decode(F.Status, 'A', 'Available', 'B', 'Full', 'Available') as "Stat",
                F.Gate 
         from flights F, airports A1, airports A2 
         where to_number(To_Char(F.schedule, 'HH24MI')) > 
               to_number(To_Char(sysdate, 'HH24MI')) and 
               F.code_from = '{@FROM}' and F.code_to = '{@TO}' and 
               F.code_from = A1.code and F.code_to = A2.code
         ]]>
  ...
  </xsql:query>
  ...
</flightFinderResult>

The listing below shows a portion of the XML returned by the XSQL Servlet by processing the following URL (it's case-sensitive):

http://localhost:7070/fly.xsql?FROM=LAX&TO=SFO&xml-stylesheet=none

This URL tells the server to invoke the XSQL Servlet and process the file fly.xsql to find flights from LAX (Los Angeles) to SFO (San Francisco) without applying a stylesheet (a useful debugging technique because it shows the raw XML code, including error messages, if any, from the database). The result is an XML document containing data from the rows in the result set (the following excerpt shows only the first row). The tags ROWSET and ROW are defined by the XSQL Servlet. The tags for each row in a rowset (for example, CODE, CODE_FROM, and DEPART_AIRPORT) come from the names of columns in database tables.

<?xml version="1.0" ?> 
  <flightFinderResult lang="english">
    <ROWSET>
      <ROW NUM="1">
        <CODE>OA0307</CODE> 
        <CODE_FROM>LAX</CODE_FROM> 
        <DEPART_AIRPORT>Los Angeles</DEPART_AIRPORT> 
        <CODE_TO>SFO</CODE_TO> 
        <SCHED>12:04</SCHED> 
        <ARRIVE_AIRPORT>San Francisco</ARRIVE_AIRPORT> 
        <STAT>Available</STAT> 
        <GATE>05</GATE> 
      </ROW>
      ...
    </ROWSET>
  ... 
  </flightFinderResult>

An XML document contains data and tags that describe the data, but no information about how to format the data for presentation. This may seem like a limitation at first glance, but it's actually a feature, and it's what makes XML so flexible. Once you have data in an XML document, you can format it any way you like.

Formatting XML with Stylesheets

The average person gets no pleasure from reading raw XML code, so the Flight Finder applies an XSLT transformation to render results in a format suitable for the end-user's client device. This section describes the process. For general information about the relationships between XML, XSLT, and the XSQL Servlet, see XSQL Pages & XSQL Servlet Release Notes.

One Stylesheet, One Target Device

The Flight Finder uses XSL stylesheets to format the XML documents that represent query results. A stylesheet is itself an XML document that specifies how to process the nodes of another XML document. The processing instructions are defined in structures called templates, and a stylesheet formats a document by applying these templates to selected nodes. For example, the XML document above contains nodes named ROWSET, ROW, CODE, etc. The following code (from flyHTMLdefault.xsl) shows how the stylesheet selects the CODE, DEPART_AIRPORT, and ARRIVE_AIRPORT nodes for each ROW in a ROWSET, and it applies templates to format the output.

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version='1.0'>
  ...
  <xsl:template match="/">
    <html>
       ...
            <xsl:for-each select="flightFinderResult/ROWSET/ROW">
              <tr>
                <td><xsl:apply-templates select="CODE"/></td>
                <td><xsl:apply-templates select="DEPART_AIRPORT"/></td>
                <td><xsl:apply-templates select="ARRIVE_AIRPORT"/></td>
                ...
              </tr>
            </xsl:for-each>
       ...
    </html>
  </xsl:template>
  <xsl:template match="CODE">
    Fly Oracle Airlines <xsl:value-of select="."/>
  </xsl:template>
  <xsl:template match="DEPART_AIRPORT">
    Leaving <xsl:value-of select="."/>
 
 </xsl:template>
  <xsl:template match="ARRIVE_AIRPORT">
    for <xsl:value-of select="."/>
  </xsl:template>
...
</xsl:stylesheet>

In this example, the formatting is simple: it just prepends a string to the contents of each node. For example, when the XSLT processor gets to the CODE node, it prepends the string "Fly Oracle Airlines " to the value of that node. The resulting HTML looks like this:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
  ...
  <TR>
    <TD>Fly Oracle Airlines OA0309</TD>
    <TD>Leaving Los Angeles</TD>
    <TD>for San Francisco</TD>
    ...
  </TR>
  ...
</HTML>

In a browser (enter the URL http://localhost:7070/fly/fly.xsql?FROM=LAX&TO=SFO&xml-stylesheet=flyHTMLdefault.xsl), the results look like this:

Many Stylesheets, Many Target Devices

XSL stylesheets are the key to multiple devices, languages, and UIs. You can include multiple <?xml-stylesheet?> tags at the top of an XSQL Page, and each of those tags can define media and href attributes to associate a user agent with an XSL stylesheet (an HTTP request includes a user-agent header that identifies the device making the request). A processing instruction without a media attribute matches all user agents so it can be used as the fallback/default.

For example, the following XML code comes from fly.xsql. It includes several <?xml-stylesheet?> tags, including one that maps the stylesheet flyVox.xsl to the Motorola Voice Browser agent, and one that maps the flyPP.xsl stylesheet to the HandHTTP (Palm Pilot) agent.

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" media="MSIE 5.0" href="flyHTML.xsl"?>
<?xml-stylesheet type="text/xsl" media="Motorola Voice Browser" href="flyVox.xsl"?>
<?xml-stylesheet type="text/xsl" media="UP.Browser" href="flyWML.xsl"?>
<?xml-stylesheet type="text/xsl" media="HandHTTP" href="flyPP.xsl"?>
<?xml-stylesheet type="text/xsl" href="flyHTMLdefault.xsl"?>

<flightFinderResult xmlns:xsql="urn:oracle-xsql" connection="fly" lang="english">
  <xsql:stylesheet-param name="lang" value="{@lang}"/>
  <xsql:query tag-case="upper">
  ...
  </xsql:query>
  ...
</flightFinderResult>

The two listings below show the XSLT code to format one result set row each for a Palm Pilot (flyPP.xsl) and a voice browser device (flyVox.xsl).

...
<xsl:for-each select="flightFinderResult/ROWSET/ROW">
  <tr>
    <td>
      <a>
        <xsl:attribute name="href">
          #<xsl:value-of select="CODE"/>
        </xsl:attribute>
        <b><xsl:value-of select="CODE"/></b>
      </a>
    </td>
    <td><xsl:apply-templates select="SCHED"/></td>
    <td><xsl:apply-templates select="GATE"/></td>
  </tr>
</xsl:for-each>
...
  <xsl:template match="CODE">
    <xsl:value-of select="."/>
  </xsl:template>
  <xsl:template match="SCHED">
    at <b><xsl:value-of select="."/></b>
  </xsl:template>
  <xsl:template match="GATE">
    gate <b><xsl:value-of select="."/></b>
  </xsl:template>
...

...
<xsl:for-each select="flightFinderResult/ROWSET/ROW">
  <step><xsl:attribute name="name">
          step<xsl:value-of select="position()"/>
        </xsl:attribute>
    <prompt>
      <xsl:apply-templates select="CODE"/>
      <xsl:apply-templates select="SCHED"/>,
      <xsl:text>Do you take that one?</xsl:text>
    </prompt>
    <input type="OPTIONLIST" name="FLIGHT">
      <xsl:choose>
        <xsl:when test="position() = @NUM">
          <option>
            <xsl:attribute name="next">
              #<xsl:value-of select="CODE"/>
            </xsl:attribute>
            <xsl:text>Yes</xsl:text>
          </option>
          <xsl:if test="position() &lt; last()">
            <option>
              <xsl:attribute name="next">
                #step<xsl:value-of select="position() + 1"/>
              </xsl:attribute>
              <xsl:text>Next</xsl:text>
            </option>
          </xsl:if>
          <xsl:if test="position() &gt; 1">
            <option>
              <xsl:attribute name="next">
                #step<xsl:value-of select="position() - 1"/>
              </xsl:attribute>
              <xsl:text>Previous</xsl:text>
            </option>
          </xsl:if>
        </xsl:when>
      </xsl:choose>
    </input>
  </step>
</xsl:for-each>
...

Localizing Output

When you invoke the Flight Finder through its portal (index.html), you can choose a language for prompts and labels. The Flight Finder supports in English, French, Spanish, and German. To do this, it uses a parameter to identify the end-user's language of choice and passes it from HTML to XSQL to XSL, then it selects the appropriate text from a file of translated messages. For example, here is an overview of how the application tracks a user's language preference (French) and selects a label in that language:

1. index.html (The user clicks a link to choose a language):
<a href="http://localhost:7070/xsql/fly/index.xsql?lang=french">Français</a>

2. index.xsql (The XSQL Page stores the user's choice in a parameter):
<xsql:set-stylesheet-param name="lang" value="{@lang}"/>

3. flyHTML.xsl (The stylesheet uses the language choice parameter to select a message from the message file):
<xsl:value-of select= "document('messages.xml')/messages/msg[@id=101 and @lang=$lang]"/>

4. messages.xml (The message file stores the translated messages):
<msg id="101" lang="french">Prochains vols sur Oracle Airlines</msg>

The following listings show these steps in context. First, index.html displays HREF links that invoke index.xsql with URLs for each supported language.

...
For Web-to-Go 
<!-- Assumes default install to c:\xsql and Flight Finder files in c:\xsql\fly --> 
<ul>
  <li type="disc">
    <a href="http://localhost:7070/xsql/fly/index.xsql">English</a>
  </li>            
  <li type="disc">
    <a href="http://localhost:7070/xsql/fly/index.xsql?lang=french">Fran&ccedil;ais</a> 
  </li>            
  <li type="disc"> 
    <a href="http://localhost:7070/xsql/fly/index.xsql?lang=spanish">Espa&ntilde;ol</a> 
  </li>            
  <li type="disc">
    <a href="http://localhost:7070/xsql/fly/index.xsql?lang=german">Deutsch</a> 
  </li>
</ul>
...

Next, the user's choice is extracted from the URL and plugged into a parameter in index.xsql. If the URL does not specify a language, a line in the following code sets it to English by default. This XSQL Page also defines a query (not shown here), which the XSQL Servlet sends to the database.

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" media="Mozilla" href="indexHTML.xsl"?>
...
<index xmlns:xsql="urn:oracle-xsql" connection="fly" lang="english">
  <xsql:set-stylesheet-param name="lang" value="{@lang}"/>
  ...
</index>

When the database returns the query results, the XSQL Servlet formats them by applying an XSLT transformation. The following code is from the stylesheet flyHTML.xsl. It includes a line that opens the message file (messages.xml) and selects message 101 for a specified language.

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version='1.0'>
  <xsl:output media-type="text/html" method="html"/>
  <xsl:param name="lang" select="@lang"/>
  <xsl:template match="/">
    <html>
      ...
      <body>
        ...
           <!-- Next available flights -->
           <xsl:value-of select=
             "document('messages.xml')/messages/msg[@id=101 and @lang=$lang]"/>
        ...
      </body>
    </html>
  </xsl:template>
  ...
</xsl:stylesheet>

The XML code below comes from messages.xml. In this file, a message represents information (such as a label or a prompt) that the Flight Finder sends to the client. Messages are identified by ID numbers, and each message is translated into each supported language. The code below shows four translations of message 101. Notice that transalations can include code for international character sets, as in the German version of the message. You may need to set your browser to display such characters; for example, in Internet Explorer, choose View > Encoding > Western European (Windows).

<?xml version="1.0"?>
<messages>
...  
  <msg id="101" lang="english">Oracle Airlines available flights</msg>
  <msg id="101" lang="french">Prochains vols sur Oracle Airlines</msg>
  <msg id="101" lang="spanish">Proximos vuelos sobre Oracle Airlines</msg>
  <msg id="101" lang="german">M&#246;gliche Fl&#252;ge mit Oracle Airlines</msg>  
...
</messages>

XML to Database

This section describes how the Flight Finder takes input from a user, converts it to XML, then writes it to the database. The first step is getting user input. The following figure shows an HTML form that displays the results of a query about flights from Los Angeles to San Francisco, and provides drop-down lists of customer names and flight codes. The user chooses a name and a code, then clicks the OK button to book that flight for that customer, and the application writes the information to the database. (This part of the application is only implemented for HTML and English.)

Here is the code from fly.xsql that populates drop-down lists named CustomerName and FlightCode with values from the database. The <form> tag includes an action attribute that specifies bookres.xsql as the file to execute to process the values when the user submits the form. The file flyHTML.xsl (not listed) provides the XSLT instructions for formatting the form as shown in the figure above.

...
  <form action="bookres.xsql" method="post">
    <field name="CustomerName">
      <xsql:query rowset-element="dropDownList"
                  row-element="listElem">
        <![CDATA[
          select unique name as "listItem"
          from customers
          order by name
        ]]>
      </xsql:query>
    </field>
    <field name="FlightCode">
      <xsql:query rowset-element="dropDownList"
                  row-element="listElem">
        <![CDATA[
          select F.code as "listItem",
            F.code as "itemId",
            A1.name as "depart_airport",
            A2.name as "arrive_airport"
          from flights F,
            airports A1,
            airports A2
          where to_number(To_Char(F.schedule, 'HH24MI')) > 
                          to_number(To_Char(sysdate, 'HH24MI')) and
            F.code_from = '{@FROM}' and
            F.code_to = '{@TO}' and
            F.code_from = A1.code and
            F.code_to = A2.code
        ]]>
      </xsql:query>
    </field>
    <sendRequest type="button" label="OK"/>
  </form>
...

After getting values from the user, the next step is to assign those values to parameters in code. The following code comes from bookres.xsql. It stores the user's choices in parameters named CustomerName and FlightCode, and defines parameters named cust and code for passing the values to XSLT stylesheets. It also uses the <xsql:dml> tag to define a SQL statement that inserts a row into the CUSTOMERS table.

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" media="Mozilla" href="bookresHTML.xsl"?>
<?xml-stylesheet type="text/xsl" media="MSIE 5.0" href="bookresHTML.xsl"?>

<bookFlight xmlns:xsql="urn:oracle-xsql" connection="fly">
  <xsql:set-stylesheet-param name="cust" value="{@CustomerName}"/>
  <xsql:set-stylesheet-param name="code" value="{@FlightCode}"/>
  <xsql:dml>
    <![CDATA[
      insert into customers values
       ('{@CustomerName}', tripseq.NEXTVAL, '{@FlightCode}')
    ]]>
  </xsql:dml>
...
</bookFlight>

The last step is to let the user know whether the operation succeeded (in this case, whether the flight was booked). The code below comes from bookresHTML.xsl. It declares parameters named cust and code to store values passed to it from bookres.xsql, then it uses those parameters to display a message to the user. (The XSLT syntax for using such parameters is $param.)

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:output media-type="text/html"/>
  <xsl:param name="cust"/>
  <xsl:param name="code"/>
  <xsl:template match="/">
    <html>
      <head>
        <title>Flight Finder</title>
      </head>
      <body>
        Booked flight #<b><xsl:value-of select="$code"/></b>
        for <b><xsl:value-of select='$cust'/></b>.
      <hr/>
      <xsl:apply-templates select="bookFlight/returnHome"/>
      </body>
    </html>
  </xsl:template>
...
</xsl:stylesheet>

Oracle Portal-to-Go

Instead of writing XSQL and XSL code yourself, you could use Oracle Portal-to-Go. A component of the Oracle Internet Platform, Portal-to-Go provides everything you need to deliver Web content to any capable device. It transforms existing content to a device's native format, and it provides a portal interface for the end-user.

Portal-to-Go uses XML to isolate content acquisition from content delivery. A Portal-to-Go portal includes services that deliver data to mobile devices, adapters that convert HTML and RDBMS content to XML, and transformers that convert XML to the appropriate markup language, including HTML, WML, TinyHTML, and voice mark-up language (VoxML). For more information, including white papers, product documentation, and a free, downloadable version of the software, visit OTN's Portal-to-Go page.

Conclusion

Oracle XML products like the XSQL Servlet make it easy to exchange data from a database to any device and back to the database. No Java programming is required. The key technologies are XML and XSLT.

< Back to Introduction

Questions or comments? Post a message in OTN's Sample Code discussion forum or send email to the author.

Thanks to Olivier Le Diouris and Steve Muench

This document is provided for information purposes only and the information herein is subject to change without notice. Please report any errors herein to Oracle Corporation. Oracle Corporation does not provide any warranties covering and specifically disclaims any liability in connection with this document.

Oracle is a registered trademark and Enabling the Information Age is a trademark or registered trademark of Oracle Corporation. All other company and product names mentioned are used for identification purposes only and may be trademarks of their respective owners.

Oracle Corporation
World Headquarters
500 Oracle Parkway
Redwood Shores, CA 94065
U.S.A.

Worldwide Inquiries:
+1.650.506.7200
Copyright © Oracle Corporation 2000
All Rights Reserved