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 8-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 8-4 Flight Finder: HTML Form Displaying Results of a Query About Flights From Los Angeles, to San Francisco

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 8-5 Flight Finder: Notifying User that Flight Was Booked

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>