4
Customizing Presentation with XML and XSQL: Flight Finder

This chapter contains the following sections:

XML Flight Finder Sample Application: Introduction

XML Flight Finder fetches data about airline flights and customizes the results for the client device (PC, cell phone, PDA,...). It is built on Oracle9i and leverages Oracle XSQL Servlet, hence this application can submit SQL queries and define output formats using XML, XSL, and XSQL text files -- no Java programming is required, and there is no code to compile. This application is easy to build, customize, and maintain.

Download the source code for XML Flight Finder to study and modify. You can also read an article that describes how the Flight Finder uses Oracle XML products and technologies, and there's a page of links to sites where you can download software that lets you simulate, for example, a cell phone on your PC.

This information and the application download is also available at:

http://otn.oracle.com/sample_code/index.htm
http://otn.oracle.com/tech/xml/xsql_servlet/index.htm then select Sample Code

What's Needed to Run XML Flight Finder

To build and run the XML Flight Finder application you need the following:

Java 1.2 or higher.
Oracle8i 8.1.5 or higher.
A version of SQL*Plus compatible with your database.
Oracle XSQL Servlet (includes Web-to-Go personal Web server for Windows NT). Download the latest version from OTN.
Flight Finder files. Download fly.zip.
A Web browser. For best results, use one that can process XML (such as Internet Explorer 5).
(Optional) Software that simulates other devices (such as a cell phone) on a computer.
(Optional) Apache or Oracle9i Application Server. While Web-to-Go is all you need to run the Flight Finder on your own machine under Windows NT, you can also run the Flight Finder under Apache or Oracle9i Application Server.

How Flight Finder Works

Flight Finder queries the database for information about flights from one city to another, then returns the results in a format customized for your end-user's device. Built on Oracle9i, Flight Finder uses the following products and technologies:

SQL, the standard for accessing business data
Oracle XSQL Servlet, which processes queries defined in XSQL pages. XSQL pages are XML documents that contain SQL code. XSQL Servlet outputs the result set as XML.
XSLT, which defines an open standard for transforming XML for target devices.

This chapter describes how Flight Finder application was implemented. You can use these techniques in any Web-based application that:

Receives requests from any client device on the Web.
Delivers database content to multiple devices.
Writes input from multiple devices back to the database.

Figure 4-1 shows how Flight Finder works.

Figure 4-1 XML Flight Finder

Text description of adxml082.gif follows

Text description of the illustration adxml082.gif

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.
The Web server invokes the XSQL Servlet with an XSQL Page.
The XSQL Servlet parses the XSQL page and queries the database.
The database returns the query results, which the XSQL Servlet converts to an XML document.
The XSQL Servlet transforms the XML by applying an XSL stylesheet appropriate for the end-user's client device.
The Web server returns the customized document to the client.

With Oracle9i, 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 Oracle9i Lite to store and retrieve XML data. You can also run these components on a middle tier such as Oracle9i Application Server, or on the client.

Flight Finder Queries the Database -- Converts Results to XML

This section describes how Flight Finder queries the database and converts the result set to an XML document. Flight Finder application consists of XSQL Pages and XSL stylesheets:

XSQL Pages define queries
XSL stylesheets format the query results.

There is no Java code in the Flight Finder--it delegates processing chores to Oracle XSQL Servlet.

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 XSQL Servlet to Process Queries and Output Result as XML

XSQL Servlet processes SQL queries and outputs the result set as XML.

It is implemented as a Java servlet and takes as input an XSQL page. This is an XML file containing embedded SQL queries. It uses XML Parser for Java and XML- SQL Utility for Java to perform many of its operations.

For example, the following code is from fly.xsql. It is XML with some special <xsql> tags for the XSQL Servlet to interpret.

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.

Note:

XSQL pages use the XSLT syntax {@param} to indicate a parameter.

Figure 4-2 shows the Flight Finder browser form and how it is used to enter FROM information (Los Angeles) and TO information (San Francisco).

Figure 4-2 Using XSQL Servlet to Process Queries and Output Result as XML: Entering FROM and TO on the Flight Finder Browser Form

Text description of fffromto.jpg follows.

Text description of the illustration fffromto.jpg

<?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. This is 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).

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

Flight Finder applies an XSLT transformation to render the XML 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 XSQL Servlet, see XSQL Pages and XSQL Servlet Release Notes on Oracle Technology Network (OTN), http://otn.oracle.com/tech/xml

One Stylesheet, One Target Device

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 foregoing XML document contains nodes named ROWSET, ROW, CODE, and so on. 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).

Figure 4-3 shows the results displayed on the browser after the stylesheet has been applied to the XML.

Figure 4-3 Flight Finder: Results After Formatting the XML with Stylesheets

Text description of ffresult.jpg follows.

Text description of the illustration ffresult.jpg

Many Stylesheets, Many Target Devices

XSL stylesheets are the key to multiple devices, languages, and user interfaces. 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).

XSLT Code From flyPP.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>
  ...

XSLT Code from flyVox.xsl:

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

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>

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

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.

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

1 Taking the User's Input

The first step is getting user input.

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

Figure 4-4 Flight Finder: HTML Form Displaying Results of a Query About Flights From Los Angeles, to San Francisco

Text description of ffbookit.jpg follows.

Text description of the illustration ffbookit.jpg

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

2 Assign Values Acquired From User to Code Parameters

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>

3 Let User Know if Operation Succeeded

The last step is to let the user know whether the operation succeeded, in this case, whether the flight was booked as shown in.

Figure 4-5 Flight Finder: Notifying User that Flight Was Booked

Text description of ffbkmsg.jpg follows.

Text description of the illustration ffbkmsg.jpg

The following code is 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>

Using Oracle9i Application Server Wireless Edition (Portal-to-Go)

Instead of writing XSQL and XSL code yourself, you can use Oracle9i Application Server (AS) Wireless Edition (Oracle Portal-to-Go).

A component of the Oracle Internet Platform, Oracle9i AS Wireless Edition 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 and can be developed on Oracle JDeveloper.

Oracle9i AS Wireless Edition uses XML to isolate content acquisition from content delivery.

A Oracle9i AS Wireless Edition portal includes the following components:

Services that deliver data to mobile devices
Adapters that convert HTML and RDBMS content to XML
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 Oracle9i AS Wireless Edition page at http://otn.oracle.com/products/iaswe.

4 Customizing Presentation with XML and XSQL: Flight Finder