Oracle8i Application Developer's Guide - XML Release 3 (8.1.7) Part Number A86030-01 |
|
Customizing Presentation with XML and XSQL: Flight Finder, 5 of 8
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:
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.
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) );
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.
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).
<?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. |
|