Oracle8i Application Developer's Guide - XML Release 3 (8.1.7) Part Number A86030-01 |
Library | Service | Contents | Index

Customizing Presentation with XML and XSQL: Flight Finder, 5 of 8

## 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 8-2 shows the Flight Finder browser form and how it is used to enter FROM information (Los Angeles) and TO information (San Francisco).

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

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

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index