Setting Up the Connection Definitions

XSQL Pages refer to database connections by using a "nickname" for the connection defined in the XSQL configuration file. Connection names are defined in the <connectiondefs> section of XSQLConfig.xml file like this:

:
  <connectiondefs>
    <connection name="demo">
      <username>scott</username>
      <password>tiger</password>      
<dburl>jdbc:oracle:thin:@localhost:1521:testDB</dburl>      
<driver>oracle.jdbc.driver.OracleDriver</driver>
    </connection>
    <connection name="prod">
      <username>prodapp</username>
      <password>sumpti0us</password>      
<dburl>jdbc:oracle:thin:@localhost:1521:prodDB</dburl>
      <driver>oracle.jdbc.driver.OracleDriver</driver>
    </connection>
</connectiondefs>
:

Any number of <connection> elements can be placed in this file to define the connections you need. An individual XSQL Page refers to the connection it wants to use by putting a connection="xxx" attribute on the top-level element in the page (also called the "document element").

Single Query Page

For a page with a single query this looks like:

<?xml version="1.0"?>
<xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
    SELECT * FROM DUAL
</xsql:query>

Multiple Query Page

For a page with more than one query, it looks like the following:

<?xml version="1.0"?>
<page connection="demo" xmlns:xsql="urn:oracle-xsql">
  <xsql:query>SELECT 'X' as X FROM DUAL</xsql:query>
  <xsql:query>SELECT 'Y' as Y FROM DUAL</xsql:query>
</page>

You can use these techniques to publish an XML-based product catalog.

XSQL Page Common Tags

Table 19-1 lists three useful and common XSQL Servlet tags. For a full list of tags see Oracle8i XML Reference.

Table 19-1 XSQL Common Tags

XSQL Tag Examples	Description
<xsql:query>	To include XML-based SQL query result in the template wherever you want, use <xsql:query> tags.
<?xml-stylesheet?>	To associate an XSL stylesheet to the page, add <?xml-stylesheet?> instructions line at the top of the file.
<xsql:action>	To extend the set of actions that can be performed to assemble the "datapage", use the <xsql:action> element.

Save the file and request it through your browser to get immediate results.

Additional XSQL Page Example

Here is another simple example of an XSQL Page. This could be in response to a URL request such as:

http://yourcompany.com/AvailableFlightsToday.xsql?City=NYC

An example XSQL Page:

<?xml version="1.0"?>
  <xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
     SELECT Carrier, FlightNumber, Origin, TO_CHAR(ExpectedTime,'HH24:MI') Due
        FROM FlightSchedule
        WHERE TRUNC(ArrivalTime) = TRUNC(SYSDATE)
        AND Destination = '{@City}'
        ORDER BY ExpectedTime
  </xsql:query>

To return the information in HTML or other XML format that complies with a DTD, add a <?xml-stylesheet....?> tag such as:

<?xml version="1.0"?>
  <?xml-stylesheet type="text/xsl" href="FlightList.xsl"?>
   <xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">
      SELECT Carrier, FlightNumber, Origin, TO_CHAR(ExpectedTime,'HH24:MI') Due
        FROM FlightSchedule
        WHERE TRUNC(ArrivalTime) = TRUNC(SYSDATE)
        AND Destination = '{@City}'
        ORDER BY ExpectedTime
  </xsql:query>

Note: {@City} is a parameter based on City=NYC. The parameter is made up of actions comprised of handles. The actions make it easy to assemble and transform information. The handles allow stylesheets to be transformed.

Built-in XSQL Action Elements

Table 19-2 lists several supplied built-in XSQL Action Elements

.

Table 19-2 Built-in XSQL Action Elements

Action Element	Description
<xsql:ref-cursor-function>	Includes the canonical XML representation of the result set of a cursor returned by a PL/SQL stored function. Use the new dynamic SQL features that are built-in to PL/SQL in Oracle8i, with <xsql:set-page-param>.
<xsql:set-page-param>	Set a page-level (local) parameter that can be referred to in subsequent SQL statements in the page. The value can be set using a static value, the value of another parameter, or the results of a SQL statement.
<xsql:include-param>	Include a parameter and its value as an element in your XSQL page.
<xsql:set-session-param>	Set an HTTP-Session level parameter. The value can be set using a static value, the value of another parameter, or the results of a SQL statement.
<xsql:set-cookie>	Set an HTTP Cookie. The value can be set using a static value, the value of another parameter, or the results of a SQL statement.
<xsql:insert-param>	Inserts the value of a single parameter containing XML. Can optionally supply a transform to get it in canonical format.