Using XSQL Pages

Producing Dynamic XML Documents from SQL Queries with <xsql:query>

To use Oracle XSQL Pages for SQL queries, include <xsql:query> taga in your XML file wherever you want the SQL executed.

The <xsql:query> element is replaced by the XML output of your query. The XSQL Page Processor finds an attribute named connection on your XML document's element whose value must match the name of a connection defined in your XSQLConfig.xml file.

• Include a Connection="conname" Attribute. You must include a connection="connname" attribute on the document element of your XSQL page. If you do not, the XML file will be served to the requestor, but no <xsql:query> tags will be processed.

• Connection name value must match XSQLCOnfig.xml. Ensure that the value supplied for the connection name matches one of the entries in the XSQLConfig.xml configuration file in xsql/lib/.

A simple example using <xsql:query> tag is:

<?xml version="1.0"?>
<xsql:query xmlns:xsql="urn:oracle-xsql" connection="demo">
    SELECT 'Hello World' AS "GREETING" FROM DUAL
</xsql:query>

This produces the following XML document:

<?xml  version = '1.0'?>
  <ROWSET>
    <ROW id="1">
      <GREETING>Hello World</GREETING>
     </ROW>
  </ROWSET>

Any number pf <xsql:query> tags can be in an .xsql page. They can also be nested among other XML tags. But the .xsql must still be a well-formed XML document.

For example, you could build up a "data page" out of two queries like this:

<?xml version="1.0"?>
   <sales-by-year xmlns:xsql="urn:oracle-xsql" connection="salesdb">
     <period id="H1" year="CY99">
       <xsql:query>
         SELECT salesperson, SUM(sales) AS Total
           FROM sales
           WHERE sale_date between '01-JAN-99' and '30-JUN-99'
           GROUP BY salesperson
       </xsql:query>
     </period>
     <period id="H2" year="CY99">
       <xsql:query>
         SELECT salesperson, SUM(sales) AS Total
           FROM sales
           WHERE sale_date between '01-JUL-99' and '31-DEC-99'
           GROUP BY salesperson
       </xsql:query>
     </period>
   </sales-by-year>

This gives the following results:

<?xml version="1.0"?>
  <sales-by-year connection="salesdb">
     <period id="H1" year="CY99">
       <ROWSET>
         <ROW id="1">
           <SALESPERSON>Steve</SALESPERSON>
           <TOTAL>23465500</TOTAL>
         </ROW>
         <ROW id="2">
           <SALESPERSON>Mark</SALESPERSON>
           <TOTAL>39983400</TOTAL>
         </ROW>
       </ROWSET>
     </period>
     <period id="H2" year="CY99">
       <ROWSET>
         <ROW id="1">
           <SALESPERSON>Steve</SALESPERSON>
           <TOTAL>67788400</TOTAL>
         </ROW>
         <ROW id="2">
           <SALESPERSON>Mark</SALESPERSON>
           <TOTAL>55786990</TOTAL>
         </ROW>
       </ROWSET>
     </period>
   </sales-by-year>

You can customize many of the aspects of the XML query results produced for each <xsql:query> in your .xsql page by supplying one or more optional attributes on the appropriate <xsql:query> tag whose XML-results you'd like to affect.

Customizing Your Query with XML-SQL Utility Query Attribute Options

The following attribute-based options correspond to features offered by the underlying oracle.xml.sql.query, OracleXMLQuery class. This class is provided in Oracle XML-SQL Utility (XSU) for Java

Table 19-4 lists the <xsql:query> tag attributes supported by the XSQL Page Processor. These are case-sensitive.

Table 19-4 <xsql:query: Tag Attributes

Attribute Name	Description
rowset-element	Element name to use for the query results. Set equal to the empty string to suppress printing a document element.	<ROWSET>
row-element	Element name to use for each row in the query results. Set equal to the empty string to suppress printing a row element.	<ROW>
max-rows	Maximum number of rows to fetch from the query. Useful for fetching the "top-N" or, in combination with skip-rows, the "next-N" rows from a query result.	fetch all rows
skip-rows	Number of rows to skip over before returning the query results.	not skip any rows
id-attribute	Attribute name for the id attribute for each row in the query result.	id
id-attribute-column	Column name to use to supply the value of the id attribute for each row in the query result.	use the row count as the id attribute value
null-indicator	If set to y or yes, causes a null-indicator attribute to be used on the element for any column whose value is NULL.	omit the element in the result for any column with a NULL value tag-case