Oracle9i XML Product Catalog Application

XMLType datatype

Functional indexes for XMLType columns

We can create functional index on the extract function as :
CREATE INDEX book_title on category_products (prodxml.extract('//TITLE/text()').getClobVal());

With the above index, the SQL query would use the functional index to evaluate the predicate instead of parsing the XML document per row and evaluating the XPATH expression.

XPATH for searching XML documents

The Search Feature in the application makes use of XPATH engine features to search the Catalog XML documents.

XPath is a W3C standard way to navigate XML documents. XPAth models the XML document as a tree of nodes. Applying an XPath expression to an XML document can result in a set of nodes. For instance, /BOOK/TITLE/ selects out all the "TITLE" child elements under the "BOOK" root element of the document.

Using extract() function with XPATH

The Extract function on XMLType, extracts the node or a set of nodes from the document identified by the XPath expression. The extracted nodes may be elements, attributes or text nodes. When extracted out all text nodes are collapsed into a single text node value. You can use the getStringVal() or getNumberVal() methods on XMLType to extract this scalar data.

For example you can use: SELECT e.prodxml.extract('//TITLE/text()').getStringVal() from category_products e;
which will retrieve the string value of all data of the element TITLE.

SYS_DBURIGEN, DB-URI-refs and sys.dburitype

The Browse Feature of the application makes use of the above features.

Oracle9i has also introduced a new concept called DBUri-refs. These are references into columns and rows of tables and views inside the database itself. DB-Uri-ref references local objects through which we can access any row or column data in any table or view in the database.

DBUri reference can be created by specifying the path expression to the constructor or the UriFactory methods. However, you also need methods to generate these DBUri references dynamically given target columns. For this purpose a new SQL function, called SYS_DBURIGEN(), has been introduced.

For example: we can use this fucntion to generate a URL of dburitype:
SELECT prodname, REPLACE(SYS_DBURIGEN(productid,proddesc).getUrl(),'''','''''') as product FROM category_products

This will create the dburitype URLs. In the application, we get a list of products for a selected category as links which are actually the dburitype URLs created using above mentioned example. To get the value of the above URLs, we need to use sys.dburitype.getclob() function as follows:

SELECT sys.dburitype('{@urivariable}',null).getclob() as answer from dual;

This will give value of the above create db-uritypes urls.

XML Parser for Java V2

Oracle provides XML parsers for Java which is a stand-alone XML component that parses an XML document (or a standalone DTD or XML Schema) so that it can be processed by an application. The application uses the DOM APIs along with the java parser to parse the XML document. The application validates the input Product XML document against its associated schema.

Generating PDF document using Apache FOP

Using the XSQL Pages framework's support for custom serializers, the oracle.xml.xsql.serializers.XSQLFOPSerializer is provided for integrating with the Apache FOP processor (http://xml.apache.org/fop). The FOP processor renders a PDF document from an XML document containing XSL Formatting Objects (http://www.w3.org/TR/xsl).

In this sample, we can see the PDF document that shows the details of all the products for a selected category.

Database Connection using in XSQL page

XSQL pages by default will read the 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.

In this sample, we are using XSQL connection manager factory implementation for making database connections. By this we are securing database sensitive information. With the above implementation, there is no need to use connection definitions in XQLConfig.xml file. But the class implementing this will have to be specified in the XSQLConfig.xml.

For the sample we are using the below connection manager implementation class with the connection pool parameters.

<connection-manager>
<factory>oracle.otnsamples.ProductCatalog.xml.xsql.actions.MyXSQLConnectionManagerFactoryImpl</factory>
</connection-manager>

<connection-pool>
<initial>10</initial>
<increment>1</increment>
<timeout-seconds>60</timeout-seconds>
<dump-allowed>no</dump-allowed>
</connection-pool>

XSQL Page Custom Action Handlers

XSQL Pages framework allows custom actions to be invoked as part of page processing. For example, to perform DML operations we can write our own action handler instead of using the built-in action handlers. The application uses Action Handlers for saving, updating and deleting the XML instance document into the database.

To create a custom Action Handler, a class should implement the oracle.xml.xsql.XSQLActionHandler interface. The custom action handlers should extend oracle.xml.xsql.XSQLActionHandlerImpl. Some of the methods of the
XSQLActionHandlerImpl class used in the application are :

• getActionElement which returns the current action element being handled
• getAttributeAllowingParam to retrieve the attribute value from an element, resolving any XSQL lexical parameter references that in the attribute's value.
• variableValue which returns the value of a lexical parameter, taking into account all scoping rules which might determine its default value.
• addResultElement which Simplifies appending a single element with text content to the root of the action handler result content.
• getPageRequest.getJDBCConnection() which gets the JDBC connection in use by this page.
  
  

Custom XSQL Connection Managers

You can provide a custom connection manager to replace the built-in connection management mechanism. To provide a custom connection manager implementation, you must provide:

1. A connection manager factory object that implements the oracle.xml.xsql.XSQLConnectionManagerFactory interface.
2. A connection manager object that implements the oracle.xml.xsql.XSQLConnectionManager interface.

Your custom connection manager factory can be set to be used as the default connection manager factory by providing the classname in the XSQLConfig.xml file.