Using the IBM Informix XSLT and Web DataBlades

This article presents an in-depth discussion of the Informix support for XML, including generating XML from database tables using the Web DataBlade, transforming incoming XML to XML, HTML or other text formats using the XSLT DataBlade, and using XML data as input to insert data into database tables. Examples are included.

Share:

Susan Cline (clines@us.ibm.com), Software Engineer, IBM Informix Extender and DataBlade development

Susan L. Cline is a software engineer in Extender and DataBlade Development. Her experience with DataBlades goes back to Illustra and continues with Informix. Susan has many years experience with Web-enabling technologies, including the IBM Informix Web DataBlade and the IBM Informix XSLT DataBlade modules. You can reach Susan at mailto:clines@us.ibm.com



28 August 2001

Introduction

IBM® Informix® can generate, transform and consume XML. Generating XML from database tables is a feature of the Web DataBlade. Transforming incoming XML to XML, HTML or other text formats is made possible through the use of the XSLT DataBlade. In this article I will provide examples that show how to use these two DataBlades to achieve generation and transformation of XML. In another example I will illustrate the Informix ability to consume XML by showing how to use XML data as input to insert data into database tables.

In the examples illustrated below, we occasionally found it necessary to break a line of code for viewing purposes. We show those inserted line breaks with "\\" characters. Be sure to remove those characters and bring the line of code up if you are copying the code. Better yet, download the samples to avoid the problem altogether.


Informix support for XML

This section gives brief introductions to XML, XSLT, the Web DataBlade and the XSLT DataBlade.

Brief introduction to XML and XSLT

Extensible Markup Language (XML) is a text format derived from Standard Generalized Markup Language (SGML). XML was originally designed to simplify electronic publishing, but has become most widely used to exchange data on the Web and within and between businesses.

The XML 1.0 specification, www.w3.org/TR/REC-xml, defines some conditions an XML document must adhere to. This is important because the XML parser uses these rules to correctly parse an XML document and will stop processing the document if it fails to do so. Some of the more important rules (but not all of them) are listed below.

  • The first element in an XML document (document element) must contain the entire document.
  • All elements must be nested. An element started inside another element must be closed, or end before another element is started.
  • All attributes must be quoted.
  • XML tags are case sensitive, which is unlike HTML.
  • All tags must have an end tag; therefore an empty tag can contain the end marker.
  • Although not absolutely required, an XML document should start with an XML declaration.

Listing 1 below is an example of a well-formed XML Document, order.xml. A well-formed XML document is one that adheres to the XML 1.0 specification.

Listing 1. A well-formed XML document
<?xml version="1.0"?>
<ORDER NUM="4389">
  <CUSTOMER>
    <NAME>Frances Hill</NAME>
    <COMPANY>SportsGear, Inc.</COMPANY>
    <PHONE>734-555-3484</PHONE>
    <FAX />
  </CUSTOMER>
  <PART>
    <ID>543</ID>
    <NAME>Batting Helmets</NAME>
    <QUANTITY>3</QUANTITY>
    <PRICE>87.00</PRICE>
  </PART>
  <PART>
    <ID>34</ID>
    <NAME>Bat Stand</NAME>
    <QUANTITY>1</QUANTITY>
    <PRICE>133.00</PRICE>
  </PART>
</ORDER>

Listing 2 below is an example of a document, bad_order.xml, that is not well formed.

Listing 2. XML document that is not well formed
<?xml version="1.0"?>
<ORDER NUM=4389>
  <CUSTOMER>
    <NAME>Frances Hill</NAME>
    </COMPANY>
    <PHONE>734-555-3484</PHONE>
    <FAX>
  </CUSTOMER>
  <PART>
    <ID>543</ID>
    <NAME>Batting Helmets</NAME>
    <QUANTITY>3
      <PRICE>87.00
     </QUANTITY>
    </PRICE>
    <ID>34</ID>
    <NAME>Bat Stand</NAME>
    <QUANTITY>1</QUANTITY>
    <PRICE>133.00</PRICE>
  </PART>
</ORDER>

Comparing order.xml and bad_order.xml, we can see that bad_order.xml breaks several rules for well-formedness:

  • The NUM attribute is not quoted. order.xml quotes the NUM attribute correctly.
  • The COMPANY tag is not closed correctly. If no COMPANY information were available the element could have been left empty by specifying the <COMPANY /> tag or equivalently <COMPANY></COMPANY>.
  • The PHONE tag is not matched by an equivalent ending PHONE tag. The ending tag is lowercase phone and therefore is not valid.
  • The FAX tag is not closed.
  • The PRICE and QUANTITY tags are not nested, but instead overlap each other.

Extensible Stylesheet Language (XSL) is a language for representing stylesheets. It is made up of three parts:

  • XSL Transformations (XSLT), a language for transforming XML documents
  • XML Path Language (XPath), a language used by XSLT to refer to parts of the XML document
  • XSL Formatting Objects (XSL-FO), an XML vocabulary for specifying formatting semantics (sometimes XSL-FO is referred to as XSL)

An XSL stylesheet uses XSLT, XPath and XSL to specify how the document is to be transformed into another XML document, or other format, via the XML Parser. The XSLT processor reads an XML document and an XSLT document, and applies the XSLT to the XML. The XSLT processor is generally built on top of the XML parser.

Two popular API's used to parse XML documents are the Document Object Model (DOM) and Simple API for XML (SAX). The API used in the XSLT DataBlade is DOM, so a brief discussion of how DOM works will help in understanding how XML documents are parsed by the XML processor and subsequently processed by the XSLT processor.

DOM builds a tree view of the document and uses the document element or root element as the root or base of the tree. When a DOM parser processes an XML document, it creates the objects representing the contents of the document, arranges these objects in a tree, and then parses the entire document. Some of the terminology used in the DOM specification will help you to understand the language used when constructing XSL stylesheets.

Node
This is the base type of DOM. Element, document, text, comment and attr all extend from the Node type.
Document
This is the DOM representation of the XML document. With the Document object you can traverse through the DOM tree to get all the elements, attributes, text and so on of the XML document.
Element
This is an element in the XML document. An example of an element in order.xml is <NAME>Frances Hill</NAME>
Attr
This is an attribute in the XML document. The only attribute in order.xml is NUM.
Text
This is text in the XML document. Any text in the XML document is a Text Node, even if it is the text of an Element or Attribute.

Brief introduction to the Web DataBlade

The Web DataBlade module is a collection of SQL functions, data types, tags and client applications that enable you to create Web applications that dynamically retrieve data from an Informix database. The Web DataBlade module allows you to dynamically access the database without writing CGI applications. By creating HTML pages that include Web DataBlade module tags and functions, the SQL statements you specify are dynamically executed and formatted as HTML. The HTML pages you create are called Application Pages or AppPages.

AppPages are stored in the Informix database. When an application makes a request for an AppPage, it is first retrieved from the database, then it is executed by an SQL function that interprets the AppPage tags and functions. The function most likely will retrieve or update information in the database and then finally formats the result as HTML.

Product Architecture

The Web DataBlade consists of three components:

  • Webdriver
  • The WebExplode() function
  • Tags and Attributes

Webdriver is a database client application that builds the SQL queries that execute the WebExplode() function to retrieve the AppPages from the database. Webdriver returns the HTML that results from the calls to the WebExplode() function to the Web server.

The WebExplode() function parses AppPages that contain AppPage tags within HTML and dynamically builds and executes the SQL statements and processing instructions embedded in the AppPage tags. The WebExplode() function formats the results of these SQL statements and processing instructions and returns the resulting HTML page to the client application, Webdriver.

Included with the Web DataBlade module are a set of SGML-compliant tags and attributes that enable SQL statements to be executed dynamically within AppPages. One example of an AppPage tag is the MISQL tag, which allows you to execute an SQL statement and format the results of the statement in the AppPage. Some of the attributes of the MISQL tag are SQL, COND, ERR and XMLDOC. The XMLDOC attribute allows you to output XML as the result of the SQL SELECT statement.

Brief introduction to the XSLT DataBlade

The XSLT DataBlade module creates an Informix user-defined routine (UDR) that uses libxslt, the Gnome C XSLT library. xslt_udr implements many of libxslt's xsltproc command-line tool's options. Many of the examples in this paper discuss how to use the XSLT DataBlade to transform XML documents using XSLT stylesheets as input. These examples assume you have read and installed the XSLT DataBlade as discussed in http://www.alphaworks.ibm.com/tech/xsltblade.

The XSLT DataBlade creates several UDRs that take an XML document and an XSL stylesheet as input -- including xslt_udr(), xslt_udr_AsClob() and xslt_udr_AsHtml(). xslt_udr() returns the result as the lvarchar type, xslt_udr_AsClob as clob and xslt_udr_AsHtml() as HTML.

Since the Web DataBlade allows for dynamic creation of XML documents this means using xslt_udr_asHtml() from within a Web DataBlade query allows for completely dynamic generation of an output document. First the XML document is generated from the Web DataBlade, then the XML is used as input for xslt_udr_AsHtml() along with an XSL stylesheet that can be created dynamically or retrieved from a database table. Next xslt_udr_AsHtml() parses and transforms the XML document per the input stylesheet's instructions and returns a document which could be XML, HTML or some other format.


Software requirements

Here are the software requirements for implementing Informix support for XML:

  • Informix Dynamic Server® (IDS) release 9.30.UC1 or higher.
    The tracing UDR in the XSLT DataBlade takes advantage of new mi_* memory trace functions, which first became available in 9.30.UC1.
  • The XSLT DataBlade module distribution, which can be downloaded from alphaWorks®.
    This contains DataBlade module installation files, examples, and functional tests.
  • The Web DataBlade module version 4.13.?C2 or higher (where the ? is a platform-specific code).
  • Installation of APB2.0 from the Web DataBlade
  • Creation of a Web Datablade Webdriver mapping.
    This will allow for calling the AppPages via a browser.
  • Web server/Webdriver

Getting started with the examples

Make sure you have fulfilled the Software requirements section above and then download the examples.

Downloading the examples

Download the xslt_udr_examples.tar.Z compressed tar archive.

Uncompress it:

uncompress xslt_udr_examples.tar.Z

Extract the tar archive:

tar xvf xslt_udr_examples.tar

The distribution is extracted into a directory hierarchy under xslt_udr_examples. The directory contents are summarized in Table 1 below.

Table 1. Directory contents from extracted tar file
READMEThis file in flat text format
sql/SQL scripts and data for examples
documents/XML and XSL Documents, Web DataBlade AppPages

Loading sample data

Prior to running the examples we need some sample data in our database tables.

All of the stylesheets, XML documents and AppPages have been provided as part of the distribution in the directory structure mentioned above.

cd into the sql directory and locate the create_tables.sql file as well as the load_tables.sql file.

The below command assumes you have created a database called demodb and have registered the XSLT DataBlade in that database. Make sure to register the Web DataBlade in your database prior to running the examples. Note: Your database does not need to be called demodb, but for ease of illustration all further examples will use the name "demodb".

Run this command to create and load the tables from the operating system command line:

dbaccess demodb create_tables.sql

The create_tables.sql file creates the tables shown in Listing 3:

Listing 3. Tables created by create_tables.sql
create table ecommerce
(
  code integer,
  desc lvarchar,
  ecomm dec(10,2),
  total dec(10,2)
);
create table customer 
( 
  name lvarchar, 
  company lvarchar, 
  phone lvarchar, 
  fax lvarchar, 
  order_num integer,
  primary key (order_num)
);
create table part 
( 
  id integer, 
  name lvarchar, 
  quantity integer, 
  price decimal(2), 
  order_num integer,
  primary key (id), 
  foreign key (order_num) references customer (order_num)
);
create table style_sheets
(
  style_title    lvarchar,
  style_doc  lvarchar
);
create table style_sheets_html
(
  style_title    lvarchar,
  style_doc  html
);
create table style_sheets_clob
(
  style_title    lvarchar,
  style_doc  clob
);
create table xml_docs
(
 xml_title    lvarchar,
 xml_doc  lvarchar
);
create table xml_docs_html
(
 xml_title    lvarchar,
 xml_doc  html
);
create table xml_docs_clob
(
 xml_title    lvarchar,
 xml_doc  clob
);

The ecommerce, part and customer tables will represent our sample application data, the style_sheets table will hold our static stylesheets and the xml_docs table will hold static XML data. The style_sheets and xml_docs tables were created three ways to demonstrate the use of xslt_udr(lvarchar, lvarchar), xslt_udr_AsHtml(html, html) and xslt_udr_AsClob(clob,clob) user defined routines (UDRs).

Note: The style_sheets table may have already been created if you followed the examples in the xslt_udr.1.0 documentation.

Next run the load_tables.sql file to populate the tables with our sample stylesheets and XML documents.

dbaccess demodb load_tables.sql

Verifying the installation

If the table creation failed, it's possible the Web DataBlade was not registered in the database. To verify the XSLT (xslt_udr) and Web DataBlades have been registered in your database, run the dblademgr command.

% blademgr
InformixServerName> list demodb

The output should look something like this:

DataBlade modules registered in database demodb:
web.4.13.UC2	xslt_udr.1.0

If no DataBlades are registered, the output would look like this:

There are no modules registered in database demodb.

If this occurs the first thing to do is to make sure you have installed the DataBlades in the Informix Server. To check this from blademgr, issue this command:

InformixServerName> show modules

The output should look something like this, with the two required modules being web.4.13.UC2 and xslt_udr.1.0:

6 DataBlade modules installed on server test:
	LLD.1.20.UC2          ifxrltree.2.00
     ifxbuiltins.1.1        spatial.8.11.UC1
	xslt_udr.1.0            web.4.13.UC2
If a module does not show up, check the prepare log.

If you have not installed the DataBlades on your server, refer to the DataBlade Module Installation and Registration Guide.

To verify your data has been loaded into the ecommerce and style_sheets tables, cd to the sql directory and run this command:

dbaccess demodb check_install.sql

The output should look similar to Listing 4:

Listing 4. Output from check_install.sql
code   311
desc   Food Products 
ecomm  54837.00
total  434261.00

style_title  ecommerce_ROOT.xsl

style_title  ecommerce_ROW.xsl 

style_title  ecommerce_ecomm.xsl 

xml_title  order.xml 

xml_title  bad_order.xml 

xml_title  ecommerce.xml

If you do not see the above output check to make sure you have successfully created your tables by issuing this SQL:

select tabname from systables where tabname like 'style%' 
    OR tabname like 'xml%' 
	OR tabname like  'ecommerce%';

This should show the style_sheets, style_sheets_html, style_sheets_clob, xml_docs, xml_docs_html, xml_docs_clob and ecommerce tables.


Readying the Web DataBlade to create XML

There are two ways to enable the Web DataBlade to serve XML data. The best way to achieve this objective is to update the wbExtensions table that is created when installing APB2.0 in your database. APB2.0 is not automatically installed when registering the Web DataBlade. If you have not installed APB2.0, refer to the Chapter, "Using AppPage Builder," in the document IBM Informix Web DataBlade Module Application Developer's Guide to complete this step prior to going further with the examples.

The second way is to manually add the HTTPHEADER to your Web DataBlade AppPage. I will discuss each method below.

Creating an XML extension

To update the wbExtensions table, refer to the IBM Informix Web DataBlade Module Application Developer's Guide. The section entitled "Generating XML-Formatted Data" explains in detail how to do so. Table 2 below specifies the values to add to the wbExtensions table. These values are best added by using the Admin Menu, Add Extension option of Application Page Builder (APB), 2.0.

Table 2. Variable Names/Values for the wbExtensions Table
Variable nameValue
Extensionxml
NameXml Page
Source TablewbPages
Super Typetext
Sub Typexml
ID ColumnID
Content Columnobject
Path Columnpath
Retrieval MethodWebExplode

HTTPHEADER

The second way to enable the Web DataBlade to serve XML data is to use the HTTPHEADER Variable-Processing Function. Here is an example of using the HTTPHEADER function within an AppPage to specify the page type as XML:

<?MIVAR>$(HTTPHEADER,content-type,text/xml)<?/MIVAR>

The next section shows complete examples of AppPages generating XML output.


Generating XML from the Web DataBlade

The MISQL AppPage tag has 3 XML-specific attributes to help you format your XML document:

  • The XMLDOC attribute is required to generate an XML stream and specifies the Document type of the XML data. As mentioned above in Brief Introduction to XML and XSLT, the Document is the root or base element of the XML document. The value specified for the XMLDOC attribute names the root element tag for the output XML document.
  • The XMLROW attribute delineates each row returned from the SQL query. It is not required and defaults to an element tag named ROW if not specified. Each XMLROW element has an attribute named ID with its row number.
  • The last XML-specific attribute of the MISQL tag is the XMLVERSION attribute, which is not mandatory. It defaults to version 1.0.

Using the XMLDOC attribute of MISQL

The example below is an AppPage, example1.xml, that shows the use of the XMLDOC attribute of the MISQL AppPage tag:

<?MISQL SQL="select code, desc, ecomm, total from ecommerce;" 
XMLDOC="TABLE">$1<?/MISQL>

Prior to viewing the XML output we need to insert example1.xml into our Web DataBlade module registered database. I have provided a script with the download examples, AppPage.sh, that allows for inserting, updating and deleting AppPages from your database. The script is available in the xslt_udr_examples/documents directory.

Follow one of the two methods below to insert example1.xml into the database:

  • Use AppPage.sh to add AppPages, using these steps:
    1. cd to the documents directory
    2. Run the following command to insert example1.xml into your database:
      pre class="code">
      AppPage.sh demodb example1 insert xml

      Note: The database, demodb, must have the Web DataBlade registered in it and you must have created the XML extension in demodb prior to running the above command.

      AppPage.sh can insert, update and delete entries from the wbPages table using files as input. The wbPages table holds the AppPages for the database.

      The syntax for AppPage.sh is:

      AppPage.sh db_name AppPage_filename <insert | update | delete> 
        <html | xml | svg>
      Note: The Application Page filename on the file system should end in an xml, html or svg extension, but when executing the command above do not add the html, xml or svg extension. For instance, the AppPage filename in this example is example1.xml, but the command is AppPage.sh demodb example1 insert xml.
  • Use Application Page Builder, 2.0. Follow these steps:
    1. Go to the Main Menu of APB.
    2. Select Add Object.
    3. For the type of object, select AppPage.
    4. Scroll to the bottom of the page and select the Browse button under Enter filename to import AppPage from.
      • Browse to the file, example1.xml in the xslt_udr_examples/documents directory and press the Continue button.
    5. Go to the upper part of the browser, and below the Save button on the Add AppPage page do the following:
      • Leave the Path text field value set to "/".
      • For the Page ID text field enter "example1".
      • For the extension type drop down box select xml.
      • Press the Save" button to save the AppPage to the database

To verify you have actually inserted the row into the database, cd to the xslt_udr_examples/sql directory and execute the following command:

dbaccess -e demodb example1.sql

The results should look something like this:

select Id, extension, object from wbPages where ID='example1'


id         example1
extension  xml
object     <?MISQL SQL="select code,desc,ecomm,total from ecommerce;"
XMLDOC="TABLE">$1<?/MISQL>

Using MISQL from dbaccess

Another way to generate XML is to use dbaccess and call the Web DataBlade WebExplode UDR directly. The resulting XML could be saved to a file or displayed. Below is the SQL, saved as xslt_udr_examples/sql/dbaccess_xml.sql. The WebExplode UDR is the UDR called by the Web DataBlade when an AppPage is executed. The first argument is the AppPage or HTML that contains Web DataBlade tags, including MISQL tags, and the second argument is additional variables to pass WebExplode in the form of name-value pairs.

dbaccess -e demodb dbaccess_xml.sql

execute function WebExplode('<?MISQL sql="select * from \\
  ecommerce" XMLDOC=TABLE>$1<?/MISQL>', '');


(expression)  <?xml version="1.0" ?><TABLE><ROW ID="1"><code>311</code><desc>Fo
              od Products</desc><ecomm>54837.00</ecomm><total>434261.00</total>
              </ROW><ROW ID="2"><code>312</code><desc>Beverage and tobacco</des
              c><ecomm>42862.00</ecomm><total>112055.00</total></ROW><ROW ID="3
              "><code>313</code><desc>Textile mills</desc><ecomm>5214.00</ecomm
              ><total>51770.00</total></ROW>

             ... output removed

              </TABLE>

The next section will describe how to view the XML output from the AppPage we just inserted into the database.


Viewing XML

Not all Web browsers display XML. Microsoft® Internet Explorer 5.x and above allow you to select a link, enter a URL or launch an XML file from a folder.

Netscape 6.0 or higher does not really support display of XML, although you can right click on an XML document in the browser to "View Page Source." The display is correctly formatted XML, but it is not displayed in a hierarchical format like Internet Explorer does.

Using Internet Explorer to view XML

Let's use Internet Explorer to view the XML output created by executing our example1.xml Application Page.

When you view an XML document in Internet Explorer, you will see that the root and child elements are color coded. Also, a plus (+) or minus sign (-) to the left of the element can be clicked to expand or collapse the element structure.

The URL below is an example of a correctly formatted request to send to a Web server in order to execute Webdriver to retrieve and execute the example1.xml AppPage:

http://www.myserver.com/xslt_demo/?MIval=/example1.xml

This URL consists of the Web server alias, www.myserver.com, the Webdriver mapping, xslt_demo, and the value of the AppPage we wish to retrieve and execute, example1.xml. Whenever an AppPage is retrieved from the Web DataBlade, the query string of MIval=AppPage_name must be added. The Webdriver mapping, xslt_demo in this case, must exist in the database where you are storing your AppPages. This means a mapping name of your choice must exist for the database where the example1.xml AppPage resides.

If you are not familiar with creating Webdriver mappings for your database, please refer to the chapter "Configuring Webdriver" in IBM Informix Web DataBlade Module Administrator's Guide, Version 4.13. This chapter describes how to create Webdriver mappings prior to attempting to generate XML via a URL similar to the one above.

Listing 5 shows partial output of calling the URL above using Internet Explorer 5.x:

Listing 5. Calling URL using Internet Explorer 5.x
<?xml version="1.0" ?> 
- <TABLE>
- <ROW ID="1">
<code>311</code> 
<desc>Food Products</desc> 
<ecomm>54837.00</ecomm> 
<total>434261.00</total> 
</ROW>
- <ROW ID="2">
<code>312</code> 
<desc>Beverage and tobacco</desc> 
<ecomm>42862.00</ecomm> 
<total>112055.00</total> 
</ROW>
.... additional output not shown
- <ROW ID="11">
<code>325</code> 
<desc>Chemicals</desc> 
<ecomm>52974.00</ecomm> 
<total>451580.00</total> 
</ROW>
</TABLE>

To demonstrate the use of IE's formatting of the data, click on the minus (-) sign to the left of the first ROW element, which has an attribute of 1.

The ROW element is now collapsed and the minus (-) sign becomes a plus (+) sign denoting there are additional elements contained within the ROW element, as shown in Listing 6.

Listing 6. Formatting of the data in Internet Explorer
<?xml version="1.0" ?> 
- <TABLE>
+ <ROW ID="1">
- <ROW ID="2">
<code>312</code> 
<desc>Beverage and tobacco</desc> 
<ecomm>42862.00</ecomm> 
<total>112055.00</total> 
</ROW>
- <ROW ID="3">
<code>313</code> 
<desc>Textile mills</desc> 
 
... additional output removed

Viewing XML documents via Internet Explorer is helpful for finding errors in your XML. For example, open the file we looked at earlier, bad_order.xml, contained in the documents directory, using IE.

IE gives an explanation of the first error it comes across in the XML document:

The XML page cannot be displayed 
Cannot view XML input using XSL style sheet. Please correct the error 
and then click the Refresh button, or try again later. 


---------------------------------------------------------------------

A string literal was expected, but no opening quote character 
was found. Line 2, Position 12 
 

<ORDER NUM=4389>
-----------^

To demonstrate that IE actually parses the XML document, let's fix the NUM attribute of ORDER so its value is quoted, and then view bad_order.xml again:

The XML page cannot be displayed 
Cannot view XML input using XSL style sheet. Please correct the error 
and then click the Refresh button, or try again later. 


---------------------------------------------------------------------

End tag 'COMPANY' does not match the start tag 'CUSTOMER'. 
Line 5, Position 7 
 

</COMPANY>
------^

Looking at bad_order.xml you can see that IE recognizes that this well-formedness rule is broken:

  • All elements must be nested. An element started inside another element must be closed, or end before another element is started.

The closing COMPANY tag does not have a beginning COMPANY tag within the beginning and closing CUSTOMER tags and therefore breaks this nesting rule.

Later on, when creating stylesheets which output XML, it will be helpful to use Internet Explorer to view the output XML. This is especially true if multiple stylesheets are called during processing. For instance, one stylesheet might format the incoming data as XML and another might format this XML data into HTML. If errors are occurring in the output HTML it is helpful to view the intermediary XML data.


Creating XSLT stylesheets

This section briefly explains XSLT stylesheets, discusses how to use the command line tool xsltproc, and shows three example stylesheets. The examples below assume you are somewhat familiar with XSL stylesheets. They are presented to refresh your knowledge in the context of using the XSLT DataBlade module.

For an excellent introduction to XML read the developerWorks tutorial, Introduction to XML, by Doug Tidwell.

For an overview of what XSLT is, read Michael Kay's paper, What kind of language is XSLT?

For a more detailed explanation of XSL refer to Manipulating data with XSL, by Nicholas Chase. Although this article has examples written in Java which are not applicable to the XSLT DataBlade, much of the information is useful to help understand XSLT data manipulation.

Another XSLT reference is What is XSLT?, by G. Ken Holman.

Brief explanation of stylesheets

Let's look at some of the tags required to create a very simple XSLT stylesheet. An XSL stylesheet is an XML document; therefore the XML declaration is required.

<?xml version="1.0" ?>

Here is the required root element for an XSL document.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/ \\
Transform">

One of the most important elements in XSL is the template element. Templates are rules used to define the transformation of the XML document by matching a node or nodes in the XML document tree. Here is an example of an xsl:template tag that contains a match attribute:

<xsl:template match="/">

The next example utilizes the xsl:template element.

Example stylesheet 1

Let's write our first stylesheet to operate on some sample XML data contained in the file ecommerce.xml, which is located in the documents directory. Listing 7 below is a partial listing of ecommerce.xml:

Listing 7. ecommerce.xml
<?xml version="1.0" ?>
<TABLE>
  <ROW ID="1">
    <code>311</code>
    <desc>Food Products</desc>
    <ecomm>54837.00</ecomm>
    <total>434261.00</total>
  </ROW>
  <ROW ID="2">
    <code>312</code>
    <desc>Beverage and tobacco</desc>
    <ecomm>42862.00</ecomm>
    <total>112055.00</total>
  </ROW>
  <ROW ID="3">
    <code>313</code>
    <desc>Textile mills</desc>
    <ecomm>5214.00</ecomm>
    <total>51770.00</total>
  </ROW>
  ... several elements not shown
  <ROW ID="11">
    <code>325</code> 
    <desc>Chemicals</desc> 
    <ecomm>52974.00</ecomm> 
    <total>451580.00</total> 
  </ROW>
</TABLE>

The root element of our XML document is TABLE. When using templates in XSL to transform the XML document, the match attribute is used to determine which elements are subject to that rule. The notion of "root" is similar to the root concept in the UNIX® file system in that it is the beginning of the structure. Similar to the "/" designation in UNIX, the root element in XSLT is also designated by a "/". A simple stylesheet, ecommerce_ROOT.xsl, that matches only the root element of any XML document, looks like this:

<?xml version="1.0" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/ \\
Transform">

<xsl:template match="/">
    Matched the root node.
</xsl:template>

</xsl:stylesheet>

Using xsltproc to transform XML

To transform the input XML by the XSL stylesheet we need an XSLT processor. Included in the XSLT DataBlade module distribution is a command line tool called xsltproc. xsltproc is part of libxslt, the Gnome C XSLT Library, which xslt_udr is based on.

Although it is not necessary to use xsltproc if you are going to use xslt_udr to transform XML, it is useful to understand the output that xsltproc will produce for your stylesheets. Not only is this helpful in debugging stylesheets that are not producing the correct output, it is often helpful to verify the results you expect. What you think you are asking the stylesheet to transform may not be the outcome you achieve. Therefore, we will look at the output for our first stylesheet and XML file using xsltproc.

The most basic usage for xsltproc is: xsltproc <stylesheet> <xmlFile>

Below is the output from running the command: xsltproc ecommerce_ROOT.xsl ecommerce.xml:

<?xml version="1.0"?>

    Matched the root node.

What the output above shows is that there is only one root node, and that it was matched only once by the template rule, match="/". We know it was matched only once because of the way an XML document is parsed and processed. The document is parsed in the order it appears, and if any other nodes matched the output, 'Matched the root node.' would appear each time. The next example will further demonstrate the way the XML document is parsed and processed.

Example stylesheet 2

Let's write another XSL document that relates more specifically to the ecommerce data. Listing 8 shows an XSL document, ecommerce_ROW.xsl, that matches each ROW element and displays each ID attribute of each ROW element.

Listing 8. ecommerce_ROW.xsl
<?xml version="1.0" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/ \\
  Transform">

<xsl:template match="ROW">
    Matched the ROW element. The ID attribute of the ROW element is \\ 
	<xsl:value-of select="@ID" />
</xsl:template>

</xsl:stylesheet>

Before we look at the output from applying this stylesheet to ecommerce.xml, notice that there are two differences between this stylesheet and the previous one, ecommerce_ROOT.xsl.

The first difference is the template match rule. Instead of matching the root element, we want to match the ROW element and therefore changed match="/" to match="ROW" within the xsl:template tag. The second difference is the introduction of the XSL tag, xsl:value-of. The value-of tag returns a string which is the value of an XPath expression. It has a required attribute of "select" which takes the XPath expression as input. Although we haven't discussed expressions yet, one of the simpliest forms of an expression is either an element or attribute (a node). In this case we wish to display the ID attribute of the ROW element and use the @ symbol in front of the attribute name to designate the value of that particular attribute.

Listing 9 is the output from running xsltproc ecommerce_ROW.xsl ecommerce.xml:

Listing 9. Output from xsltproc ecommerce_ROW.xsl ecommerce.xml
<?xml version="1.0"?>

  
    Matched the ROW element. The ID attribute of the ROW element is 1
  
    Matched the ROW element. The ID attribute of the ROW element is 2
  
    Matched the ROW element. The ID attribute of the ROW element is 3
  
    ... several lines removed from output.
  
    Matched the ROW element. The ID attribute of the ROW element is 10
  
    Matched the ROW element. The ID attribute of the ROW element is 11

Example stylesheet 3

Our last example stylesheet in this section, ecommerce_ecomm.xsl, introduces the idea of XPath expressions. As I mentioned above, XML Path Language (XPath) is a language used by XSLT to refer to parts of the XML document. In this example we will display all of the ROW element data that matches the criteria of having a value for ecomm that is larger than 20,000. In addition, for the elements that meet this criteria we'll display the percentage of total ecomm they represent.

Listing 10 shows ecommerce_ecomm.xsl:

NOTE: "\\" characters denote continuation of code to the next line.

Listing 10. ecommerce_ecomm.xsl
<?xml version="1.0" ?> 
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/ \\
Transform">

<xsl:template match="/">
    <xsl:variable name="total_ecomm" select="sum(//ecomm)"/>
    <xsl:variable name="rowList" select="//ROW"/>
    <xsl:call-template name="percent_ecomm">
        <xsl:with-param name="total_ecomm" select="$total_ecomm" />
        <xsl:with-param name="rowList" select="$rowList" />
    </xsl:call-template>
</xsl:template>

<xsl:template name="percent_ecomm">
   <xsl:param name="total_ecomm" />
   <xsl:param name="rowList" />
   <xsl:choose>
       <xsl:when test="$rowList">
         <xsl:if test="$rowList[1]/ecomm > 20000">

             code:<xsl:value-of select="$rowList[1]/code" />
             desc:<xsl:value-of select="$rowList[1]/desc" />
             ecomm:<xsl:value-of select="$rowList[1]/ecomm" />
             percent of total ecomm:<xsl:value-of select= \\
	  "format-number ($rowList[1]/ecomm div $total_ecomm,'#.00')"/>

         </xsl:if>
         <xsl:call-template name="percent_ecomm">
             <xsl:with-param name="total_ecomm" select="$total_ecomm" />
             <xsl:with-param name="rowList" select="$rowList[position()  \\
			   > 1]" />
         </xsl:call-template>
       </xsl:when>
   </xsl:choose>
</xsl:template>
</xsl:stylesheet>

This stylesheet is more complex then the previous stylesheets, so we will explain the functionality of it, without going into great detail. The references mentioned above may be helpful if you are not familiar with XSLT.

Briefly, I'll explain how the incoming XML is transformed when this stylesheet is applied to it.

The first xsl:template tag matches the root node and is matched once since there is only one root element. At that point two variables, total_ecomm and rowList are created. total_ecomm calculates the sum of the value of all the ecomm elements in the stylesheet. rowList is a node set that contains all of the ROW elements.

Next the template named percent_ecomm is called with the parameters total_ecomm and rowList. The percent_ecomm template has some conditional processing to determine when all of the nodes in rowList have been processed.

<xsl:when test="$rowList">

If there are still nodes in rowList, then the test function is used to determine if the first node in the node set with a child element of ecomm is greater than 20000.

<xsl:if test="$rowList[1]/ecomm > 20000">

If this condition does occur, then the text "code:" with the value of the code element in the current rowList element (which is ROW) is output as a string. The same occurs for desc and ecomm. The last bit of text to appear is "percent of total ecomm:" along with the formatted number (as a string) of the value of the ecomm element divided by the total ecommerce amount which is in the total_ecomm variable constructed above.

code:<xsl:value-of select="$rowList[1]/code" />
desc:<xsl:value-of select="$rowList[1]/desc" />
ecomm:<xsl:value-of select="$rowList[1]/ecomm" />
percent of total ecomm:<xsl:value-of select="format-number( \\
 $rowList[1]/ecomm div $total_ecomm,'#.00')"/>

The next lines of the stylesheet calls the template named percent_ecomm (itself) again, since we are still in the block of the stylesheet where there are more nodes to process in the rowList node set. Recursion is a useful tool in stylesheets and often is the only way to achieve the desired results.

<xsl:call-template name="percent_ecomm">
    <xsl:with-param name="total_ecomm" select="$total_ecomm" />
    <xsl:with-param name="rowList" select="$rowList[position() \\
	 > 1]" />
</xsl:call-template>

The second to last line above, where the param rowList is passed back to the percent_ecomm template, sets the rowList param to contain all of the nodes in the rowList variable, except for the one just processed. Every time the percent_ecomm template is called, only the first node in the node set is processed, which is denoted by the $rowList[1]. This means only the "top" or first node of the node set is processed. When the template is called again, the rest of the node set, less the one just processed, is passed as the param. Each iteration through the call of the template slims down the node set by one until all nodes are eventually processed.

Once there are no more nodes to process in the rowList node set, the processing returns to the calling template (match="/"), which does not specify any more instructions, and the transformation is complete.

<xsl:template match="/">
    <xsl:call-template name="percent_ecomm">
        <xsl:with-param name="total_ecomm" select="$total_ecomm" />
        <xsl:with-param name="rowList" select="$rowList" />
    </xsl:call-template>
</xsl:template>

Now that we have seen the purpose of the stylesheet let's use xsltproc to look at the results.

xsltproc ecommerce_ecomm.xsl ecommerce.xml
Listing 11. xsltproc results
<?xml version="1.0"?>



             code:311
             desc:Food Products
             ecomm:54837.00
             percent of total ecomm:.24

             code:312
             desc:Beverage and tobacco
             ecomm:42862.00
             percent of total ecomm:.18

             code:322
             desc:Paper
             ecomm:20617.00
             percent of total ecomm:.09

             code:324
             desc:Petroleum and coal products
             ecomm:24770.00
             percent of total ecomm:.11

             code:325
             desc:Chemicals
             ecomm:52974.00
             percent of total ecomm:.23

This section briefly explained XSLT stylesheets, showed three sample stylesheets, and showed the results of transforming XML with these stylesheets using xsltproc. In the next section we will use the XSLT DataBlade with the same stylesheets to transform XML.


Executing xslt_udr from dbaccess

This section will show how to use xslt_udr(), xslt_udr_AsHtml() and xslt_udr_AsClob() to transform XML using the same stylesheets we have just discussed. All of our data is in the database tables, so transforming XML is simply issuing an SQL SELECT statement.

Selecting the XML and XSLT document from the database

The file ecommerce_ROOT.sql contains this SQL:

Listing 12. ecommerce_ROOT.sql
execute procedure ifx_allow_newline('t');

select xslt_udr(s.style_doc, x.xml_doc) 
FROM style_sheets s, xml_docs x
WHERE s.style_title = 'ecommerce_ROOT.xsl' AND 
x.xml_title = 'ecommerce.xml';

select xslt_udr_AsHtml(s.style_doc, x.xml_doc) 
FROM style_sheets_html s, xml_docs_html x
WHERE s.style_title = 'ecommerce_ROOT.xsl' AND 
x.xml_title = 'ecommerce.xml';

select xslt_udr_AsClob(s.style_doc, x.xml_doc) 
FROM style_sheets_clob s, xml_docs_clob x
WHERE s.style_title = 'ecommerce_ROOT.xsl' AND
x.xml_title = 'ecommerce.xml';

Each of the sample SQL files in this section demonstrates the use of the three primary UDR's available with the XSLT DataBlade. When viewing the output in dbaccess, xslt_udr() and xslt_udr_AsHtml() put line breaks in seemingly random places. xslt_udr_AsClob() formats the results exactly as the XSLT document specifies when viewed through dbaccess.

The SQL in Listing 12 executes xslt_udr, which takes the style_doc and xml_doc columns as parameters and selects the correct xml_doc and style_doc by using the WHERE clause to limit the results.

Listing 13 shows the output from executing the following command:

dbaccess demodb ecommerce_ROOT.sql
Listing 13. Output from ecommerce_ROOT.sql
(expression)  <?xml version="1.0"?>

    Matched the root element. 

 



(expression)  <?xml version="1.0"?>

    Matched the root element. 

 



(expression)  
<?xml version="1.0"?>

    Matched the root element.

The next example selects the ROW elements along with the ID attribute. This is the stylesheet ecommerce_ROW.xsl, shown in Listing 14. For brevity only the clob output is shown, in Listing 15.

dbaccess demodb ecommerce_ROW.sql
Listing 14. ecommerce_ROW.xls
execute procedure ifx_allow_newline('t');

select xslt_udr(s.style_doc, x.xml_doc) 
FROM style_sheets s, xml_docs x
WHERE s.style_title = 'ecommerce_ROW.xsl' 
AND x.xml_title = 'ecommerce.xml';

select xslt_udr_AsHtml(s.style_doc, x.xml_doc) 
FROM style_sheets_html s, xml_docs_html x
WHERE s.style_title = 'ecommerce_ROW.xsl' 
AND x.xml_title = 'ecommerce.xml';

select xslt_udr_AsClob(s.style_doc, x.xml_doc) 
FROM style_sheets_clob s, xml_docs_clob x
WHERE s.style_title = 'ecommerce_ROW.xsl' 
AND x.xml_title = 'ecommerce.xml';
Listing 15. Output from ecommerce_ROW.xls
(expression)  
<?xml version="1.0"?>

  
    Matched the ROW element. The ID attribute of the ROW element is 1
  
    Matched the ROW element. The ID attribute of the ROW element is 2
  
    Matched the ROW element. The ID attribute of the ROW element is 3
  
    Matched the ROW element. The ID attribute of the ROW element is 4
  
    Matched the ROW element. The ID attribute of the ROW element is 5
  
    Matched the ROW element. The ID attribute of the ROW element is 6
  
    Matched the ROW element. The ID attribute of the ROW element is 7
  
    Matched the ROW element. The ID attribute of the ROW element is 8
  
    Matched the ROW element. The ID attribute of the ROW element is 9
  
    Matched the ROW element. The ID attribute of the ROW element is 10
  
    Matched the ROW element. The ID attribute of the ROW element is 11

The final example shows the results from transforming ecommerce.xml with the stylesheet ecommerce_ecomm.xsl by executing the xslt_udr_AsClob UDR with those parameters:

dbaccess demodb ecommerce_ecomm.sql
Listing 16. ecommerce_ecomm.sql file
execute procedure ifx_allow_newline('t');

select xslt_udr(s.style_doc, x.xml_doc) 
FROM style_sheets s, xml_docs x
WHERE s.style_title = 'ecommerce_ecomm.xsl' 
AND x.xml_title = 'ecommerce.xml';

select xslt_udr_AsHtml(s.style_doc, x.xml_doc) 
FROM style_sheets_html s, xml_docs_html x
WHERE s.style_title = 'ecommerce_ecomm.xsl' 
AND x.xml_title = 'ecommerce.xml';

select xslt_udr_AsClob(s.style_doc, x.xml_doc) 
FROM style_sheets_clob s, xml_docs_clob x
WHERE s.style_title = 'ecommerce_ecomm.xsl' 
AND x.xml_title = 'ecommerce.xml';
Listing 17. Results from executing ecommerce_ecomm.sql
(expression)  
<?xml version="1.0"?>


             code:311
             desc:Food Products
             ecomm:54837.00
             percent of total ecomm:.24

             code:312
             desc:Beverage and tobacco
             ecomm:42862.00
             percent of total ecomm:.18

             code:322
             desc:Paper
             ecomm:20617.00
             percent of total ecomm:.09

             code:324
             desc:Petroleum and coal products
             ecomm:24770.00
             percent of total ecomm:.11

             code:325
             desc:Chemicals
             ecomm:52974.00
             percent of total ecomm:.23

When to use xslt_udr_AsHtml() or xslt_udr_AsClob()>

The LVARCHAR data type can store up to 2048 bytes of data. If your stylesheet (or XML data) is larger than 2048 bytes, you must use the xslt_udr_AsHtml() UDR or the xslt_udr_AsClob() UDR. If the stylesheet you are using as input to xslt_udr(lvarchar, lvarchar) is greater than 2048 bytes, the rest of the stylesheet will simply be truncated and the XSLT transformation you expect to take place on your XML document will not occur properly.

The HTML data type is used to store Application Pages (AppPages) for the Web DataBlade in the database. HTML is a multirepresentational data type, which means that the size of the data determines how it will be stored internally. Since the HTML data type can hold columns of data larger than 2048 bytes, xslt_udr_AsHtml() is useful for stylesheets larger than this threshold.

You may wish to use xslt_udr_AsClob() regardless of the size of your stylesheet if you prefer to store your XSLT stylesheet and/or XML document outside of the database. The built-in server function FileToClob() allows for conversion from an operating system file to a CLOB. For instance, using the same ecommerce.xml and ecommerce_ROOT.xsl files, the following SQL would produce the transformation of the XML without the need for creating tables in the database to store the XML and/or XSLT stylesheets.

execute function 
xslt_udr_AsClob(FileToClob('ecommerce_ROOT.xsl', 'client'), \\
 FileToClob('ecommerce.xml', 'client'));

The next section will further discuss situations when it may make sense to use one form of the UDR over another.


Executing xslt_udr_AsHtml from a Web DataBlade AppPage

Selecting XSL stylesheets from the file system

The next example is more typical of how you would use xslt_udr_AsHtml in an application. We will dynamically generate the XML document using the Web DataBlade's MISQL tag with the XMLDOC attribute, and introduce the FileToHtml() UDR available in the WebDatablade to select the stylesheet from the file system. Finally, the AppPage will be executed via Webdriver and display HTML to a browser.

We'll build the AppPage in two stages. The first step is to select the table data as XML using the MISQL tag as discussed above. The only change to the line of code we had above is to put the results of this select into a variable. The second step is to execute xslt_udr_AsHtml() using the variable we just created above and to fetch the stylesheet from the file system using FileToHtml().

<?MIVAR name="xmldoc"><?MISQL SQL="select code, desc,  \\
ecomm, total from ecommerce;" 
        XMLDOC="TABLE">$1<?/MISQL><?/MIVAR>

This creates an XML document by selecting the values from the ecommerce table in the database and placing the results into the Web DataBlade variable named xmldoc.

The next step is to execute xslt_udr_AsHtml() using a new stylesheet, ecommerce_html.xsl, from the file system. The FileToHtml UDR takes the full file system path of the file as it's parameter. ecommerce_html.xsl is similar to ecommerce_ecomm.xsl which we discussed earlier, but with a few changes. The first is the addition of the <xsl:output> tag which specifies the output to be HTML. Another change is the addition of the <table>, <tr> and <td> tags to format the result as an HTML table.

Here is the additional <xsl:output> tag in the ecommerce_html.xsl stylesheet.

<xsl:output method="html" version="4.0" />

The second part of the AppPage utilizes the FileToHtml UDR and the xslt_udr_AsHtml UDR.

<?MISQL SQL="execute function xslt_udr_AsHtml(FileToHtml
  '/local0/xslt_udr_examples/documents/ecommerce_html.xsl'), \\
  '$xmldoc') ; ">$1<?/MISQL>

The finished AppPage looks like this:

<?MIVAR name="xmldoc"><?MISQL SQL="select code,  \\
 desc, ecomm, total from ecommerce;" 
        XMLDOC="TABLE">$1<?/MISQL><?/MIVAR>
<?MISQL SQL="execute function xslt_udr_AsHtml(FileToHtml
  ('/local0/xslt_udr_examples/documents/ecommerce_html.xsl'), \\
  '$xmldoc') ; ">$1<?/MISQL>

The above AppPage is in the documents directory and called ecommAppPage.html. Prior to actually inserting it into your database, the file system path for ecommerce_html.xsl will need to be edited to your actual path.

To insert ecommAppPage.html into your Web DataBlade registered database, use APB as described above, or the script provided, AppPage.sh. Here is a sample command to insert ecommAppPage.html into your database using AppPage.sh:

AppPage.sh demodb ecommAppPage insert html

This command inserts ecommAppPage.html into demodb with an ID of ecommAppPage and an extension of html. Once ecommAppPage.html has been inserted into the database, the following URL is a sample of a typical Web DataBlade URL used to execute the AppPage, ecommAppPage.html:

http://www.myserver.com/xslt_demo/?MIval=/ecommAppPage.html

Table 3 shows the HTML-formatted data as displayed through a browser:

Table 3. HTML-formatted data
CodeDescecommPercent of total ecomm
311Food Products54837.00.24
312Beverage and tobacco42862.00.18
322Paper20617.00.09
324Petroleum and coal products24770.00.11
325Chemicals52974.00.23

Note: The value for the percent of total ecomm does not equal 1.00 because some of the original ecommerce data was edited and only a subset appears in the file ecommerce.xml.

Selecting XSL stylesheets from the database

If the stylesheet ecommerce_html.xsl is stored in the database, the only change to our Application Page would be the MISQL statement:

<?MISQL SQL="select xslt_udr_AsHtml(style_doc, '$xmldoc') 
FROM style_sheets_html WHERE style_title = 'ecommerce_html.xsl'; \\
">$1<?/MISQL>

The complete AppPage is saved as ecommAppPageDB.html in the documents directory and when executed through Webdriver produces the same results as above.

Differences between xslt_udr and xsltproc when executing stylesheets

When writing an application that makes use of xslt_udr, stylesheets may be stored on the file system, in the database, or generated dynamically. The same is true for XML documents. When executing xslt_udr, certain characters must be escaped prior to executing the UDR.

For instance when inserting a value into an LVARCHAR table in the database, the quote character, " ' ", must be escaped. To insert the string 'What's for dinner?' into an LVARCHAR column in a database table, the following syntax is required:

insert into mytable values ('What''s for dinner?');

A very simple stylesheet that contains a quote, quote.xsl, is shown below.

<?xml version="1.0" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/ \\
Transform">

<xsl:template match="/">
    Where's the root element?  Here.
</xsl:template>

</xsl:stylesheet>

Executing the command line tool xsltproc against quote.xsl and ecommerce.xml shows that xsltproc is perfectly happy with a single quote:

xsltproc quote.xsl ecommerce.xml

<?xml version="1.0" ?>

    Where's the root element?  Here.

Attempting to insert the style_sheet quote.xsl into the style_sheets table will fail due to the single quote not being escaped. Listing 18 shows the results of executing the SQL in the xslt_udr_examples/sql/bad_insert.sql file:

Listing 18. Output from bad_insert.sql
dbaccess -e demodb bad_insert.sql

Database selected.

execute procedure ifx_allow_newline('t');
Routine executed.

insert into style_sheets values ('quote.xsl','<?xml version="1.0" ?> 
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/ \\
Transform">
<xsl:template match="/">
    Where's the root element?  Here.
</xsl:template>
</xsl:stylesheet>');


  201: A syntax error has occurred.
Error in line 6
Near character position 10

Database closed.

There are several ways to circumvent the problem of inserting quoted values within a column.

  • Using escaping quotes
    Escape the single quote by placing another single quote before it. This example was shown above.
  • Using methods where no escape is necessary:
    • For tables with LVARCHAR or HTML columns, use the 'load from 'loadfile' insert into mytable;' syntax.
    • For tables with an HTML column, use the 'insert into mytable values (FileToHtml('/full/path/to/myfile'));' syntax.
    • For tables with CLOB columns, use the 'insert into mytable values (FileToClob('myfile', 'client'));' syntax.

The load_tables.sql file referred to earlier inserted the quote.xsl document successfully using the 'load' and 'FileToClob' syntax to load the tables. Refer to the xslt_udr_examples/sql/load_tables.sql file for specific examples.

For example, executing the SQL below succeeds because we loaded quote.xsl using the 'load from' syntax in the load_tables.sql file:

select xslt_udr(s.style_doc, x.xml_doc) 
FROM style_sheets s, xml_docs x 
WHERE s.style_title = 'quote.xsl' and 
     x.xml_title = 'ecommerce.xml';

(expression)  <?xml version="1.0"?>

    Where's the root element?  Here.

In this section we discussed some of the differences when executing xslt_udr and xsltproc in the context of a stylesheet stored on the file system or in the database. An upcoming section describes some of the changes to make when executing a stylesheet via xslt_udr from within a Web DataBlade AppPage.


More about xsltproc

xsltproc is the command line tool included with the XSLT DataBlade and is part of libxslt, the Gnome C XSLT Library. xsltproc is useful in developing applications to check the validity of stylesheets prior to deployment in a database environment. xsltproc has optional arguments that are helpful in debugging stylesheets which are not producing the desired output.

When you are creating stylesheets for transforming XML, the first hurdle to overcome is creating a syntactically correct XML document. xsltproc is a good tool to use for checking proper XML syntax and XSLT and XPath usage. In this section we will only show xsltproc output; however, as I mentioned in the section, Using Internet Explorer to View XML, IE can be a great tool to check your XSLT stylesheets for correct XML syntax as well.

Using xsltproc for debugging stylesheets

For this example we'll start out with a stylesheet that has some typos and incorrect use of XPath expressions to see xsltproc's error reporting. By viewing the output, we can correct mistakes and come up with the correct stylesheet. Our first attempt at the stylesheet is called incorrect1.xsl.

NOTE: "\\" characters denote continuation of code to the next line.

Listing 19. incorrectl.xsl
1) <?xml version="1.0" ?> 
2) <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/ \\
   Transform">
3)
4)  <xsl:template match="/">
5)    <xsl:variable name="parts" select="//PART"/>
6)    <xsl:variable name="parts_price" select="number(0)" /> 
7)
8)    <xsl:apply-templates/>
9)
10)    <xsl:call-template name="total_cost">
11)      <xsl:with-param name="parts" select="$parts" />
12)      <xsl:with-param name="parts_price" select="$parts_price" />
13)    </xsl:call-template>
14)
15)  </xsl:template>
16)    
17)    <xsl:template name="total_cost"> 
18)       <xsl:param name="parts"/>
19)       <xsl:param name="parts_price"/>
20)       <xsl:choose>
21)         <xsl:when test="$parts">
22)           <xsl:variable name="line_item">
23)               <xsl:value-of select="number($parts[1]/PRICE *  \\
                    $parts[1]/QUANTITY)" /> 
24)           </xsl:variable>
25)           <xsl:call-template name="total_cost" >
26)             <xsl:with-param name="parts" select="$parts[position() \\
                 > 1]" />
27)             <xsl:with-param name="parts_price" select= \\
                 "$parts_price + $line_item" />
28)           </xsl:call-template>
29)        </xsl:when>
30)        <xsl:otherwise>
31)
32)    Total Cost of Order is $<xsl:value-of select="$parts_price />
33)        </xsl:otherwise>
34)      </xsl:choose>
35)  </xsl:template>
36)   
37)          
38)  <xsl:template match="//CUSTOMER">
39)    
40)    Customer Name: <xsl:value-of select="//CUSTOMER/name" /> 
41)    Customer Company: <xsl:value-of select="//CUSTOMER/COMPANY" /> 
42)  </xsl:template>
43)
44)
45)  <xsl:template match="//PART" >
46)    Part Name: <xsl:value-of select="NAME" />
47)
48)    Part Quantity: <xsl:value-of select="QUANTITY" >
49)  </xsl:template>
50)</xsl:stylesheet>

Listing 20 below shows the output from applying incorrect1.xsl to order.xml from xsltproc.

NOTE: "\\" characters denote continuation of code to the next line.

Listing 20.
xsltproc incorrect1.xsl order.xml
incorrect1.xsl:33: error: Unescaped '<' not allowed in \\
attributes values
        </xsl:otherwise>
        ^
incorrect1.xsl:33: error: attributes construct error
        </xsl:otherwise>
        ^
incorrect1.xsl:33: error: error parsing attribute name
        </xsl:otherwise>
        ^
incorrect1.xsl:33: error: attributes construct error
        </xsl:otherwise>
        ^
incorrect1.xsl:33: error: xmlParseStartTag: problem parsing \\
attributes
        </xsl:otherwise>
        ^
incorrect1.xsl:33: error: Couldn't find end of Start Tag xsl:value-of
        </xsl:otherwise>
        ^
incorrect1.xsl:49: error: Opening and ending tag mismatch: \\
xsl:value-of and xsl:template
  </xsl:template>
                ^
incorrect1.xsl:50: error: Opening and ending tag mismatch: \\
xsl:template and xsl:stylesheet
</xsl:stylesheet>
                ^
incorrect1.xsl:51: error: Premature end of data in tag 
xsl:stylesheet

^
cannot parse incorrect1.xsl

When debugging stylesheets you will find that it's usually most efficient to correct the first mistake that appears. Other syntax errors occurring after the first mistake may be caused by the first syntax error and will be resolved by fixing the first problem.

The first error in incorrect1.xsl is reported to be on line 33. The most informative comment in the error output is the last line of output about line 33:

incorrect1.xsl:33: error: Couldn't find end of Start Tag xsl:value-of
        </xsl:otherwise>
        ^

Since the select attribute of the value-of tag did not have closing double-quotes, the error was reported to be on line 33, which is the next line after the value-of tag. Frequently this is the way errors are reported; since a tag was not closed properly, the parser reports the error on the next line since the element or tag on the previous line was not formed correctly.

The other additional piece of information that is helpful is the 'attributes construct error'. This is telling you there is a problem with the way the attribute value is constructed, which is precisely the case.

For now we will correct only the first error reported above and rerun xsltproc with incorrect2.xsl which reflects this single change.

NOTE: "\\" characters denote continuation of code to the next line.

Listing 21. incorrect2.xsl
xsltproc incorrect2.xsl order.xml
incorrect2.xsl:49: error: Opening and ending tag mismatch: \\
  xsl:value-of and xsl:template
  </xsl:template>
                ^
incorrect2.xsl:50: error: Opening and ending tag mismatch: \\
  xsl:template and xsl:stylesheet
</xsl:stylesheet>
                ^
incorrect2.xsl:51: error: Premature end of data in tag xsl:stylesheet

^
cannot parse incorrect2.xsl

We can refer to incorrect1.xsl above since we only added the double quote, and the line numbers (added for clarity) remain unchanged. This error reporting is not as clear as in the previous example. Looking at line 49 all looks well:

49)  </xsl:template>

As mentioned above the syntax error generally occurs before the line number. Looking at line 48 in addition to using the information about the ending tag mismatch we can see the problem is on line 48 with the ending tag of xsl:value-of.

48)    Part Quantity: <xsl:value-of select="QUANTITY" >

Below is the proper syntax for line 48.

48)    Part Quantity: <xsl:value-of select="QUANTITY" />

The next version of the stylesheet is called incorrect3.xsl. We've fixed the first two problems, but a less obvious problem remains.

incorrect3.xsl

Listing 22. incorrect3.xsl
xsltproc incorrect3.xsl order.xml
<?xml version="1.0"?>

    
    Customer Name:  
    Customer Company: SportsGear, Inc.
  
    Part Name: Batting Helmets

    Part Quantity: 3
  
    Part Name: Bat Stand

    Part Quantity: 1


    Total Cost of Order is $394

The last problem is that no value appears for Customer Name in the output. Although our XML document might not have had a Customer Name and therefore the value might be empty, in this case we know order.xml had a name value of 'Frances Hill'.

The part of the stylesheet where the value of Customer Name is selected is shown below.

40)    Customer Name: <xsl:value-of select="//CUSTOMER/name" />

The customer element within order.xml looks like this:

  <CUSTOMER>
    <NAME>Frances Hill</NAME>
    <COMPANY>SportsGear, Inc.</COMPANY>
    <PHONE>734-555-3484</PHONE>
    <FAX />
  </CUSTOMER>

The problem with our select attribute for the value-of tag is the the XPath expression, //CUSTOMER/name. Looking at order.xml above the tag name for the child of the 'CUSTOMER' node is 'NAME', not 'name'.

This example demonstrates the use of xsltproc to catch XML and XSLT syntax errors. It also showed there are times when contextual errors, for instance using 'name' instead of 'NAME', are not reported since xsltproc has no way of knowing what you mean. Some of xsltproc's options can help you to view output that may help with contextual meaning. This can be essential when more complicated stylesheets are created, especially in the context of a Web DataBlade AppPage.

Some of xsltproc's options

Here are some options that may be helpful in debugging stylesheets.

  • xsltproc --version reports the version of libxml2 and libxslt used to build xsltproc. This information can be useful if a bug is suspected in a particular version of libxml2 or libxslt.
  • xsltproc --verbose shows the libxml2 and libxslt processing and displays the evaluation of user functions and variables in the stylesheet.
  • xsltproc --maxdepth val sets the libxslt xsltMaxDepth flag which effects the recursion depth. By default the XSLT DataBlade has a maxdepth of 50. If a stylesheet which depends on recursion is not processed correctly using xslt_udr, try setting xsltproc's maxdepth to 50 to see if the problem occurs. Slowly increase the maxdepth setting in xsltproc to see when the setting results in a favorable result. With caution, and in a development environment first, increase xslt_udr's maxdepth setting to this level.

Some, but not all, of the options supported in xsltproc are supported in xslt_udr. The section, Debugging Techniques, will discuss some of xslt_udr's options as well as xslt_TraceSet.

xsltproc verbose

xsltproc used without any options verifies the syntactic validity of the stylesheet and XML document. However, it does not display how the processing of the XML document by the stylesheet occurs. xsltproc used with the verbose option is an excellent tool to do this. The syntax for this option is:

xsltproc --verbose style_sheet xml_doc.

Here are some situations where using the verbose option of xsltproc is helpful:

  • No output is displayed when using xsltproc without options.
    This probably means the templates constructed in the stylesheet are not correct for the XML document being processed. Either a match was not successful or apply-templates may not have been called. Using the verbose option will display those templates found, those not found and those applied.
  • Output is displayed using xsltproc without options, but evaluations are incorrect.
    Function evaluations and element values are displayed during processing using the verbose option.
  • Output is displayed using xsltproc without options, but not all XML data is processed.
    The stylesheet is written incorrectly and the nodes processed are not as expected. Using xsltproc with verbose shows the number of nodes and their values during processing.

Generating dynamic XSL stylesheets from a Web DataBlade AppPage

The examples in this section dynamically create the stylesheet within the AppPage. Also, certain characters that are allowed in static stylesheets or stylesheets that are stored in the database must be escaped when they are present in the AppPage. These situations are shown in an example and outlined specifically.

Escaping characters in the stylesheet

The example in the next section shows three examples of characters and the situation in which they must be replaced or escaped. Table 4 shows some differences between creating static stylesheets and stylesheets created in a Web DataBlade AppPage.

Table 4. Static stylesheets vs. Web Datablade stylesheets
Static stylesheetWeb DataBlade, dynamic stylesheet
$xslt_variable$$xslt_variable
'literal'''literal'' - double single quotes
< or > in test condition$(WEBUNHTML,"<")

Dynamic stylesheet generating HTML

The AppPage we will create in this example selects data from a table as XML, creates a stylesheet and executes xslt_udr_AsHtml within the page to return HTML. Dynamically creating the stylesheet means we need to create the stylesheet as a Web DataBlade variable and use this variable within xslt_udr_AsHtml. One other Web DataBlade variable, WEBUNHTML, will be discussed also. The stylesheet we will build dynamically will be equivalent to the static file ecommerce_html.xsl, contained in the documents directory. Listing 23 below shows ecommerce_html.xsl:

NOTE: "\\" characters denote continuation of code to the next line.

Listing 23. ecommerce_html.xsl
<?xml version="1.0" ?> 
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/ \\
  Transform">
<xsl:output method="html" version="4.0" />

<xsl:template match="/">
    <xsl:variable name="total_ecomm" select="sum(//ecomm)"/>
    <xsl:variable name="rowList" select="/TABLE/ROW"/>
    <table>
    <xsl:call-template name="percent_ecomm">
        <xsl:with-param name="total_ecomm" select="$total_ecomm" />
        <xsl:with-param name="rowList" select="$rowList" />
    </xsl:call-template>
    </table>
</xsl:template>

<xsl:template name="percent_ecomm">
   <xsl:param name="total_ecomm" />
   <xsl:param name="rowList" />
   <xsl:choose>
       <xsl:when test="$rowList">
         <xsl:if test="$rowList[1]/ecomm > 20000">
             <tr>
             <td>code:<xsl:value-of select="$rowList[1]/code" \\
			   /></td>
             <td>desc:<xsl:value-of select="$rowList[1]/desc" \\
			   /></td>
             <td>ecomm:<xsl:value-of select="$rowList[1]/ecomm" \\
			   /></td>
             <td>percent of total ecomm:<xsl:value-of   \\
	select="format-number($rowList[1]/ecomm div $total_ecomm, \\
	    '#.00')" /></td>
             </tr>

         </xsl:if>
         <xsl:call-template name="percent_ecomm">
             <xsl:with-param name="total_ecomm" select="$total_ecomm" />
             <xsl:with-param name="rowList" select= \\
			   "$rowList[position() > 1]" />
         </xsl:call-template>
       </xsl:when>
   </xsl:choose>
</xsl:template>
</xsl:stylesheet>

The final AppPage will show the selection of the XML data, but first we'll look at the section of the AppPage that builds the stylesheet and puts it in the Web DataBlade variable, xsldoc. The line numbers to the left in Listing 24 are not part of the stylesheet but included for explanatory purposes.

NOTE: "\\" characters denote continuation of code to the next line.

Listing 24. Using AppPage to build the stylesheet
1) <?MIVAR name="xsldoc">
2) <?xml version="1.0" ?> 
3) <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/ \\
   Transform">
4) <xsl:output method="html" version="4.0" />

5) <xsl:template match="/">
6)    <xsl:variable name="total_ecomm" select="sum(//ecomm)"/>
7)    <xsl:variable name="rowList" select="/TABLE/ROW"/>
8)    <table>
9)    <xsl:call-template name="percent_ecomm">
10)        <xsl:with-param name="total_ecomm" select="$$total_ecomm" />
11)        <xsl:with-param name="rowList" select="$$rowList" />
12)   </xsl:call-template>
13)    </table>
14) </xsl:template>

15) <xsl:template name="percent_ecomm">
16)    <xsl:param name="total_ecomm" />
17)    <xsl:param name="rowList" />
18)    <xsl:choose>
19)       <xsl:when test="$$rowList">
20)         <xsl:if test="$$rowList[1]/ecomm<?MIVAR>$(WEBUNHTML, \\
             ">")<?/MIVAR>20000">
21)             <tr>
22)             <td>code:<xsl:value-of select="$$rowList[1]/code" \\
               /></td>
23)             <td>desc:<xsl:value-of select="$$rowList[1]/desc"   \\
               /></td>
24)             <td>ecomm:<xsl:value-of select="$$rowList[1]/ecomm" \\
               /></td>
25)             <td>percent of total ecomm:<xsl:value-of 
                 select="format-number  \\
                 ($$rowList[1]/ecomm div $$total_ecomm,''#.00'')"/></td>
26)             </tr>
27)
28)         </xsl:if>
29)         <xsl:call-template name="percent_ecomm">
30)             <xsl:with-param name="total_ecomm" select="$$total_ecomm" />
31)             <xsl:with-param name="rowList" select= \\
                 "$$rowList[position() > 1]" />
32)         </xsl:call-template>
33)      </xsl:when>
34)    </xsl:choose>
35) </xsl:template>
36) </xsl:stylesheet>
37) <?/MIVAR>

There are three situations in the dynamic stylesheet that require changes from the static stylesheet above. The first is the addition of another $ sign in front of the xslt variables. This is required to escape their evaluation as Web DataBlade variables. A Web DataBlade variable is referenced from within an AppPage with a single $. The xslt variables that are effected by this are on lines 10,11,19,20,22,23,24,25,30 and 31.

The second impact of placing the stylesheet within the AppPage in this example is on line 20. Line 20 in the AppPage uses the WEBUNHTML function of the Web DataBlade. Below is the line in the dynamic stylesheet followed by the corresponding line in the static stylesheet:

dynamic -  <xsl:if test="$$rowList[1]/ecomm<?MIVAR>$
           (WEBUNHTML,">")<?/MIVAR>20000">
static -   <xsl:if test="$rowList[1]/ecomm > 20000">

The WEBUNHTML function returns it's string argument with HTML characters replaced with their entity references. In XSLT when making numeric comparisions such as less than and greater than, entity references are already required so the parser does not mistake them for beginning and ending tag characters. The additional entity reference replacement is required within the AppPage so they are sent to the XSLT processor literally as > and <.

The final change required is escaping the single quotes on line 25. Since the entire Web DataBlade variable, $xsldoc, is being processed by the database in the UDR xslt_udr_AsHtml, the quotes in front of the single quotes are required to escape the single quotes. This is because of the reasons mentioned above in the section Differences between xslt_udr and xsltproc when executing stylesheets . Line 25 is shown below with two single quotes, followed by #.00, followed by two more single quotes.

<td>percent of total ecomm:<xsl:value-of  \\
  select="format-number($$rowList[1]/ecomm div \\
  $$total_ecomm,''#.00'')"/></td>

Now the only thing left to do is to generate the XML by selecting it from the ecommerce table and calling the xslt_udr_asHtml function.

<?MIVAR name="xmldoc"><?MISQL SQL="select code, desc, \\
  ecomm, total from ecommerce;" XMLDOC="TABLE">$1<?/MISQL \\
  ><?/MIVAR>

<?MISQL SQL="execute function xslt_udr_asHtml('$xsldoc', \\
 '$xmldoc');">$1<?/MISQL>

The complete AppPage is in the file dynEcommAppPage.html in the documents directory. Once it is inserted into the database using AppPage.sh or APB2.0 and executed the results are the same as above when executing ecommerce_ecomm.xsl.


Inserting incoming XML into database tables as text

This example shows how to build a stylesheet based on known XML data and a predefined table schema to insert XML data into database tables with LVARCHAR columns.

As in previous examples, the stylesheet will be created dynamically within an AppPage, although the XML data structure must be known and the table schema built prior to creating the AppPage.

XML document and table schema

The XML document we will use for this example is order.xml. It represents an order which has a unique order number, customer information and parts that comprise the order. order.xml is shown in Listing 25:

Listing 25. order.xml
<?xml version="1.0"?>
<ORDER NUM="4389">
  <CUSTOMER>
    <NAME>Frances Hill</NAME>
    <COMPANY>SportsGear, Inc.</COMPANY>
    <PHONE>734-555-3484</PHONE>
    <FAX />
  </CUSTOMER>
  <PART>
    <ID>543</ID>
    <NAME>Batting Helmets</NAME>
    <QUANTITY>3</QUANTITY>
    <PRICE>87.00</PRICE>
  </PART>
  <PART>
    <ID>34</ID>
    <NAME>Bat Stand</NAME>
    <QUANTITY>1</QUANTITY>
    <PRICE>133.00</PRICE>
  </PART>
</ORDER>

The tables that will accept the parsed XML data are called PART and CUSTOMER.

Listing 26. CUSTOMER and PART tables
CREATE table CUSTOMER 
(
    name lvarchar,
    company lvarchar,
    phone lvarchar,
    fax lvarchar,
    order_num integer,
    primary key (order_num) 
);


create table PART
(
    id integer,
    name lvarchar,
    quantity integer,
    price decimal(6,2),
    order_num integer references CUSTOMER (order_num),
    primary key (id)
);

The primary key for the CUSTOMER table is order_num while the PART table has a foreign key of order_num as well which references the CUSTOMER order_num column. Therefore one order contains one CUSTOMER entry and may contain multiple PARTS which are bound by the order_num.

XSLT stylesheet to parse XML

The stylesheet below was created knowing the structure of order.xml and the two tables, CUSTOMER and PART, which will store the data after it has been converted from XML to text. The stylesheet will create database INSERT statements to be executed within the AppPage. order.xsl is shown in Listing 27:

NOTE: "\\" characters denote continuation of code to the next line.

Listing 27. order.xsl
<?xml version="1.0" encoding="UTF-8" ?>
  <xsl:stylesheet version="1.0" 
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >

    <xsl:output method="text" />
    <xsl:variable name="allInserts"  select="' '" />

      <xsl:template match="/">
        <xsl:variable name="elementList" select="/ORDER/CUSTOMER" />
        <xsl:variable name="tableName" select="'CUSTOMER'" />
        <xsl:variable name="order_num" select="/ORDER/@NUM" />

        <xsl:call-template name="rows" >
          <xsl:with-param name="elementList" select="$elementList" />
          <xsl:with-param name="allInserts" select="$allInserts" />
          <xsl:with-param name="tableName" select="$tableName" />
          <xsl:with-param name="order_num" select="$order_num" />
        </xsl:call-template>

        <xsl:call-template name="rows" >
          <xsl:with-param name="elementList" select="/ORDER/PART" />
          <xsl:with-param name="allInserts" select="' '" />
          <xsl:with-param name="tableName" select="'PART'" />
          <xsl:with-param name="order_num" select="$order_num" />
        </xsl:call-template>

      </xsl:template>

      <xsl:template name="rows" >
          <xsl:param name="elementList" />
          <xsl:param name="allInserts" />
          <xsl:param name="tableName" />
          <xsl:param name="order_num" />

          <xsl:choose>
          <xsl:when test="$elementList" >
          <xsl:variable name="column_names" >
             <xsl:for-each select="$elementList[1]/*" >
               <xsl:if test=". != ''" > <xsl:value-of select= \\
			    "name()" /> , </xsl:if>
             </xsl:for-each>
          </xsl:variable>
          <xsl:variable name="insert_cols" select="concat \\
		    ($column_names,'ORDER_NUM')" />
          <xsl:variable name="column_values" >
             <xsl:for-each select="$elementList[1]/*" >
               <xsl:if test=". != ''" >'<xsl:value-of select="." \\
			     />', </xsl:if>
             </xsl:for-each>
          </xsl:variable>
          <xsl:variable name="values" select="concat(concat \\
		   ($column_values, $order_num), ');')" />
          <xsl:variable name="table_insert" select="concat \\
		    ('insert into ', $tableName)" />
          <xsl:variable name="insert" select="concat($table_insert, \\ 
		    concat(' (',concat($insert_cols, concat(') VALUES  \\
			 (', $values))))" />
          <xsl:call-template name="rows" >
              <xsl:with-param name="elementList" select= \\
			    "$elementList[position() > 1]" />
              <xsl:with-param name="allInserts" select= \\
			    "concat($allInserts, $insert)" />
              <xsl:with-param name="tableName" select="$tableName" />
              <xsl:with-param name="order_num" select="$order_num" />
          </xsl:call-template>
       </xsl:when>
       <xsl:otherwise>
	  <xsl:value-of select="$allInserts" />
       </xsl:otherwise>
       </xsl:choose>
      </xsl:template>

</xsl:stylesheet>

Before building the AppPage which will include this stylesheet we'll use xsltproc to check that our stylesheet is producing the results we want.

Listing 28. Stylesheet results
xsltproc order.xsl order.xml

 insert into CUSTOMER (NAME , COMPANY , PHONE , ORDER_NUM) VALUES 
('Frances Hill', 'SportsGear, Inc.', '734-555-3484', 4389); 
insert into PART (ID , NAME , QUANTITY , PRICE , ORDER_NUM) VALUES 
('543', 'Batting Helmets', '3', '87.00', 4389); 
insert into PART (ID , NAME , QUANTITY , PRICE , ORDER_NUM) VALUES 
('34', 'Bat Stand', '1', '133.00', 4389);

The results are as expected - our order.xml document had one order for one customer with two parts in the order. The order_num relates the three row entries in the database tables.

The stylesheet uses recursion to process all occurrences of CUSTOMER elements and PART elements. The template that matches the root element sets up three variables used later in the stylesheet and then calls another template twice with different values for the variables.

The first time the 'rows' template is called the CUSTOMER element is processed; the second time, the PARTS elements are processed. In the body of the 'rows' template, recursion is used to process all elements. For instance, if more PARTS elements existed in order.xml, they would be processed as well.

After all elements are processed in the 'rows' template, the <xsl:otherwise> block is executed. It outputs the value of the allInserts variable which contains the constructed string containing the SQL INSERT statement.

AppPage

The AppPage which will shred the XML must accomplish four things:

  • Accept the incoming XML document.
  • Construct the stylesheet.
  • Execute xslt_udr_AsHtml()
  • Execute the SQL statements generated from xslt_udr_AsHtml()

Additionally, when executing the page we'll add some HTML output to show the results of what happened.

The incoming XML document in a real application would be from another source - some other application that is generating or transferring XML. In our simplified example we will select the XML document from the database for clarity.

The beginning of the AppPage is shown in Listing 29 completing the first two steps, selecting the XML document from the database as the source data and modifying the static stylesheet appropriately for the AppPage. The dollar signs ($) and single quotes (') are escaped to allow for processing by the database and the Web DataBlade.

NOTE: "\\" characters denote continuation of code to the next line.

Listing 29. Beginning of the AppPage
<?MIVAR name=xmldoc><?MISQL SQL="select xml_doc from xml_docs  \\
   where xml_title = 'order.xml'";>$1<?/MISQL><?/MIVAR>

<?MIVAR name=xsldoc><?xml version="1.0" encoding="UTF-8" ?>
  <xsl:stylesheet version="1.0" 
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >

    <xsl:output method="text" />
    <xsl:variable name="allInserts"  select="'' ''" />

      <xsl:template match="/">
        <xsl:variable name="elementList" select="/ORDER/CUSTOMER" />
        <xsl:variable name="tableName" select="''CUSTOMER''" />
        <xsl:variable name="order_num" select="/ORDER/@NUM" />

        <xsl:call-template name="rows" >
          <xsl:with-param name="elementList" select="$$elementList" />
          <xsl:with-param name="allInserts" select="$$allInserts" />
          <xsl:with-param name="tableName" select="$$tableName" />
          <xsl:with-param name="order_num" select="$$order_num" />
        </xsl:call-template>

        <xsl:call-template name="rows" >
          <xsl:with-param name="elementList" select="/ORDER/PART" />
          <xsl:with-param name="allInserts" select="'' ''" />
          <xsl:with-param name="tableName" select="''PART''" />
          <xsl:with-param name="order_num" select="$$order_num" />
        </xsl:call-template>

      </xsl:template>

      <xsl:template name="rows" >
          <xsl:param name="elementList" />
          <xsl:param name="allInserts" />
          <xsl:param name="tableName" />
          <xsl:param name="order_num" />

          <xsl:choose>
          <xsl:when test="$$elementList" >
          <xsl:variable name="column_names" >
             <xsl:for-each select="$$elementList[1]/*" >
              <xsl:if test=". != ''''" > <xsl:value-of select= \\
			   "name()" /> , </xsl:if>
             </xsl:for-each>
          </xsl:variable>
          <xsl:variable name="insert_cols" select="concat  \\
		   ($$column_names, ''ORDER_NUM'')" />
          <xsl:variable name="column_values" >
             <xsl:for-each select="$$elementList[1]/*" >
              <xsl:if test=". != ''''" >''<xsl:value-of select="." \\
			    />'', </xsl:if>
            </xsl:for-each>
          </xsl:variable>
          <xsl:variable name="values" select="concat(concat \\
		   ($$column_values, $$order_num), '');'')" />
          <xsl:variable name="table_insert" select="concat \\
		   (''insert into '', $$tableName)" />
          <xsl:variable name="insert" select="concat($$table_insert, \\
		concat ('' ('',concat($$insert_cols, concat('') VALUES ('', \\
		     $$values))))" />
          <xsl:call-template name="rows" >
              <xsl:with-param name="elementList" select= \\
			   "$$elementList[position() > 1]" />
              <xsl:with-param name="allInserts" select= \\
			   "concat($$allInserts, $$insert)" />
              <xsl:with-param name="tableName" select="$$tableName" />
              <xsl:with-param name="order_num" select="$$order_num" />
          </xsl:call-template>
       </xsl:when>
       <xsl:otherwise>
        <xsl:value-of select="$$allInserts" />
       </xsl:otherwise>
       </xsl:choose>
      </xsl:template>

</xsl:stylesheet>
<?/MIVAR>

The XML document is now in the Web DataBlade variable $xmldoc and the stylesheet is in the Web Datablade variable $xsldoc. The next snippet of the AppPage shows xslt_udr_AsHtml being called with the two variables and the execution of the SQL INSERT statements returned by xslt_udr_AsHtml.

<?MIVAR name="sql_stmt"><?MISQL SQL="execute function xslt_udr_AsHtml \\
 ('$xsldoc', '$xmldoc');">$1<?/MISQL><?/MIVAR>

<?MISQL sql="$sql_stmt"><?/MISQL>

The AppPage now completes the four steps required to shred the XML and place the XML data in text columns in the database. This AppPage is in the xslt_udr_examples/documents directory and is called orderAppPage.html.

orderAppPage.html has some additional HTML code that is not shown here since it is not part of the XML processing. The results of executing orderAppPage.html once inserted into the database are shown in Table 5 below to demonstrate the INSERT statements being built and then executed.

  • Insert statement 1: insert into CUSTOMER (NAME , COMPANY , PHONE , ORDER_NUM) VALUES ('Frances Hill', 'SportsGear, Inc.', '734-555-3484', 4389);
  • Insert statement 2: insert into PART (ID , NAME , QUANTITY , PRICE , ORDER_NUM) VALUES ('543', 'Batting Helmets', '3', '87.00', 4389);
  • Insert statement 3: insert into PART (ID , NAME , QUANTITY , PRICE , ORDER_NUM) VALUES ('34', 'Bat Stand', '1', '133.00', 4389);
Table 5. Results of executing orderAppPage.html
select namecompanyphonefaxorder_num from customer;
Frances HillSportsGear, Inc.734-555-3484NULL4389
select idnamequantitypriceorder_num from part;
543Batting Helmets387.004389
34Bat Stand1133.004389

This example showed how to process incoming XML by embedding a stylesheet within a Web DataBlade AppPage, calling xslt_udr on the XML and stylesheet and executing the SQL INSERT statements generated from xslt_udr using the Web DataBlade MISQL tag.

Executing orderAppPage.html more than once will result in this error:

Dynamic Page Generation Error: Integrity constraint violation.

The reason for this is the primary key on the CUSTOMER table.


Creating dbaccess load files from incoming XML

A common way to load table data via dbaccess is to use the 'load from' syntax. For instance, the syntax to load data from a file called employee.unl into a table which has already been created is:

load from 'employee.unl' insert into employees;

This section describes another method to parse incoming XML data and load it into the database by creating load files appropriate for use with dbaccess. Two stylesheets are provided which are generic enough be used with multiple XML documents as long as a few restrictions are adhered to.

Restrictions of XML documents

The two stylesheets in this section are used for two different types of XML documents, which must be of a certain form:

  • The XML document has only one element other than the root element and contains only attributes.
  • The XML document has only one top level element other than the root element. Additionally, multiple nested elements are contained within this top level element and none of these elements can contain any attributes.

employee_attrs.xml shown in Listing 30 represents the "attribute" type of document.

NOTE: "\\" characters denote continuation of code to the next line.

Listing 30. employee_attrs.xml
<?xml version="1.0" ?>
<employees>
  <employee name="Eliza Dolittle" position="CFO" age="33" sex="f" />
  <employee name="Billy T. Kid" position="Sales Manager" age="41" sex="m" />
  <employee name="Ursula LeGuin" position="Technical Writer" \\
  age="54" sex="f" />
  <employee name="Templeton Fong" position="Marketing Manager" \\
  age="26" sex="m" />
  <employee name="Caterina Adams" position="Administrative \\
  Assistant" age="20" sex="f" />
</employees>

employee_elements.xml shown in Listing 31 represents the "element" type of document.

Listing 31. employee_elements.xml
<?xml version="1.0" ?>
<employees>
  <employee> 
    <name>Eliza Dolittle</name> 
    <position>CFO</position> 
    <age>33</age> 
    <sex>f</sex>
  </employee>
  <employee> 
    <name>Billy T. Kid</name> 
    <position>Sales Manager</position> 
    <age>41</age> 
    <sex>m</sex>
  </employee>
  <employee> 
    <name>Ursula LeGuin</name> 
    <position>Technical Writer</position> 
    <age>54</age> 
    <sex>f</sex>
  </employee>
  <employee> 
    <name>Templeton Fong</name> 
    <position>Marketing Manager</position> 
    <age>26</age> 
    <sex>m</sex>
  </employee>
  <employee> 
    <name>Caterina Adams</name> 
    <position>Administrative Assistant</position> 
    <age>20</age> 
    <sex>f</sex>
  </employee>
</employees>

XSLT stylesheets to parse XML

The two stylesheets shown in this section work on either the "element" or "attribute" type documents shown above, but not both.

Listing 32 shows the "element" type stylesheet, employee_elements.xsl, which will transform employee_elements.xml.

NOTE: "\\" characters denote continuation of code to the next line.

Listing 32. employee_elements.xsl
<?xml version="1.0" encoding="UTF-8" ?>
  <xsl:stylesheet version="1.0" xmlns:xsl= \\
  "http://www.w3.org/1999/XSL/ Transform">

   <xsl:output method="text" />
   <xsl:strip-space elements="*" />

   <xsl:template match="//*/*">
     <xsl:for-each select="*" > 
       <xsl:value-of select="." />|<xsl:text/>
     </xsl:for-each>
     <xsl:text>	</xsl:text>
   </xsl:template>

</xsl:stylesheet>

The template match attribute matches all nodes which are children of the top level node. Since we have made a restriction about the incoming XML document we know this will match only one type of element. In our XML document, employee_elements.xml, it is the "employee" node.

The for-each statement selects all of the child elements of the employee node and outputs the value of each element along with a "|" character. This character is the default column value separator for dbaccess load files.

After each element has had it's values selected in the for-each statement, a carriage return is written to the output via the xsl:text element.

The next stylesheet, employee_attrs.xsl, is very similar and transforms employee_attrs.xml.

NOTE: "\\" characters denote continuation of code to the next line.

Listing 33. employee_attrs.xsl
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/ \\
 Transform" > 

   <xsl:output method="text" />
   <xsl:strip-space elements="*" />

   <xsl:template match="//*/*">
     <xsl:for-each select="@*" > 
       <xsl:value-of select="." />|<xsl:text/>
     </xsl:for-each>
     <xsl:text>	</xsl:text>
   </xsl:template>
</xsl:stylesheet>

This stylesheet matches the same element, employee, and then selects all attributes of the employee element in the for-each statement.

Using xsltproc, xslt_udr to create load files

The xsltproc command to create an output file is:

xsltproc -o <output_file> <stylesheet> <xml file>

Listing 34 is the contents of our output file, employee_elements.unl, from executing this command:

xsltproc -o employee_elements.unl employee_elements.xsl 
employee_elements.xml
Listing 34. employee_elements.unl
%cat employee_elements.unl

Eliza Dolittle|CFO|33|f|
Billy T. Kid|Sales Manager|41|m|
Ursula LeGuin|Technical Writer|54|f|
Templeton Fong|Marketing Manager|26|m|
Caterina Adams|Administrative Assistant|20|f|

Executing the command,

xsltproc -o employee_attrs.unl employee_attrs.xsl employee_attrs.xml

produces the identical output to the above which used the employee "elements" XSL and XML documents instead of the "attributes" documents.

From within a dbaccess session, both of these SQL statements would produce equivalent output files using xslt_udr_AsHtml():

execute function xslt_udr_AsHtml(FileToHtml
('/local0/xslt_udr_examples/documents/employee_attrs.xsl'), 
FileToHtml('/local0/xslt_udr_examples/documents/employee_attrs.xml'), 
'--o /local0/xslt_udr_examples/documents/employee_attrs.unl');

execute function xslt_udr_AsHtml(FileToHtml
('/local0/xslt_udr_examples/documents/employee_elements.xsl'), 
FileToHtml('/local0/xslt_udr_examples/documents/employee_elements.xml'), 
'--o /local0/xslt_udr_examples/documents/employee_elements.unl');

SQL statements to load data

The employees table, create_employees_table.sql, was created like this:

CREATE table employees 
(
  name lvarchar, 
  position lvarchar, 
  age integer, 
  sex char(1)
);

These SQL statements, contained in employee_load.sql, could be used to load the contents of the output files into the employees table:

load from employee_attrs.unl insert into employees;
load from employee_elements.unl insert into employees;

This section showed two generic stylesheets that might be used to produce dbaccess load files if the structure of the incoming XML was relatively flat. This could be a helpful tool if loading of the XML data into the database was to be done in batches or deferred to a time other than during the transformation of the incoming XML.


Debugging techniques

Debugging stylesheets can be a laborious task at times. Adding to this complexity is the embedding of stylesheets within a Web DataBlade AppPage and the processing by the database. When you're attempting to create dynamic stylesheets within an AppPage it is best to first construct a standalone stylesheet and XML document to verify the desired results. This section will outline general steps to take when creating a database application that uses xslt_udr and the Web DataBlade to create and use stylesheets with XML data. If this process is followed, troubleshooting and debugging stylesheet problems will be easier.

Use xsltproc, xslt_udr, then AppPage

The section, Generating dynamic XSL stylesheets from a Web DataBlade AppPage, outlines the process to create an AppPage. I've listed below the steps used to create an embedded stylesheet along with suggestions for troubleshooting along the way. After listing the steps, we will go back and discuss each of them thoroughly and look at examples.

  1. Determine what your XML data looks like and create a sample file.
  2. Create a static stylesheet to transform the sample XML data.
  3. Use xsltproc and/or xsltproc --verbose to verify that the output is correct.
  4. Once the static stylesheet is okay, cut and paste the stylesheet into a Web DataBlade variable, escaping single quotes ('), dollar signs ($) and > and < in xslt test functions.
  5. Place the XML data in a Web DataBlade variable. Either select the XML data from the database or create it on the fly using the MISQL tag.
  6. Execute xslt_udr_AsHtml() within the AppPage using the two variables just created.
  7. If these results are okay, substitute Web DataBlade variables where desired in the embedded stylesheet.
  8. If the results are not as expected, use MIVAR tags to see what the Web DataBlade variables holding the XML document and the XSL stylesheet look like prior to executing xslt_udr_AsHtml().
  9. If the above doesn't help use the xslt_TraceSet() UDR with a level of 35 to see what xslt_udr_AsHtml() is being passed for the XML and stylesheet variables.

Step 1. Listing 35 shows the static xml file, bill_materials.xml.

Listing 35. bill_materials.xml
<?xml version="1.0"?>
<BOM NUM="3267" NAME="Door">
  <PART>
    <ID>36</ID>
    <NAME>Frame</NAME>
    <COST>110.00</COST>
    <MARKUP>1.25</MARKUP>
    <NUM_REQD>1</NUM_REQD>
  </PART>
  <PART>
    <ID>57</ID>
    <NAME>Jam</NAME>
    <COST>50.00</COST>
    <MARKUP>1.10</MARKUP>
    <NUM_REQD>1</NUM_REQD>
  </PART>
  <PART>
    <ID>60</ID>
    <NAME>Panels</NAME>
    <COST>30.00</COST>
    <MARKUP>1.15</MARKUP>
    <NUM_REQD>3</NUM_REQD>
  </PART>
  <PART>
    <ID>83</ID>
    <NAME>Window</NAME>
    <COST>60.00</COST>
    <MARKUP>1.30</MARKUP>
    <NUM_REQD>1</NUM_REQD>
  </PART>
  <PART>
    <ID>84</ID>
    <NAME>Strike Plate</NAME>
    <COST>20.00</COST>
    <MARKUP>1.05</MARKUP>
    <NUM_REQD>1</NUM_REQD>
  </PART>
</BOM>

Step 2. The static stylesheet, bill_materials.xsl.

NOTE: "\\" characters denote continuation of code to the next line.

Listing 36. bill_materials.xsl
<?xml version="1.0" ?> 
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/ \\
  Transform">
<xsl:output method="html" version="4.0" />

  <xsl:template match="/">
    <xsl:variable name="partList" select="/BOM/PART"/>
    <xsl:variable name="partCost" select="number(0)"/>
    <xsl:variable name="partRetCost" select="number(0)"/>
    <xsl:variable name="taxRate" select="number(1.06)"/>

    <table border="1">
    <xsl:call-template name="cost">
        <xsl:with-param name="partList" select="$partList" />
        <xsl:with-param name="partCost" select="$partCost" />
        <xsl:with-param name="partRetCost" select="$partRetCost" />
        <xsl:with-param name="taxRate" select="$taxRate"/>
    </xsl:call-template>
    </table>
  </xsl:template>

  <xsl:template name="cost">
    <xsl:param name="partList" />
    <xsl:param name="partCost" />
    <xsl:param name="partRetCost" />
    <xsl:param name="taxRate"/>
      <xsl:choose>
        <xsl:when test="$partList">
        <tr>
        <td>Part Name: <xsl:value-of select= \\
		  "$partList[1]/NAME" /></td>
        <td>Number Required: <xsl:value-of   \\
		  select="$partList[1]/NUM_REQD" /></td>
        </tr>
          <xsl:variable name="eachSupCost">
             <xsl:value-of select="($partList[1]/COST *  \\
			   $partList[1]/NUM_REQD)" />
          </xsl:variable>
          <xsl:variable name="eachRetCost">
             <xsl:value-of select="($partList[1]/COST *  \\
		$partList[1]/NUM_REQD * $partList[1]/MARKUP)" />
          </xsl:variable>
          <xsl:call-template name="cost">
            <xsl:with-param name="partList" select= \\
		"$partList[position() > 1]" />
            <xsl:with-param name="partCost" select= \\
			  "number($partCost + $eachSupCost)" />
            <xsl:with-param name="partRetCost" select= \\
			  "number($partRetCost + $eachRetCost)" />
            <xsl:with-param name="taxRate" select= \\
			  "number($taxRate)" />
          </xsl:call-template>
        </xsl:when>
        <xsl:otherwise>
        <tr><td>Tax Rate used to Calculate Total:<xsl:value-of \\ 
		  select="format-number(($taxRate - 1.0) * 100,'#.00')"  \\
		  />%</td></tr>
        <tr><td>Total <b>Supplier</b>  \\
 Cost for One Door: $<xsl:value-of select="format-number($partCost * \\ 
 $taxRate,'#.00')" /></td></tr>
        <tr><td>Total <b>Retail</b> Cost for One Door: \\
  $<xsl:value-of select="format-number($partRetCost * 
		  $taxRate,'#.00')" /></td></tr>
        </xsl:otherwise>
      </xsl:choose>
  </xsl:template>
</xsl:stylesheet>

Step 3. Verify the desired output using xsltproc. If the stylesheet is syntactically correct and is processed using xsltproc, but does not produce the correct results, use xsltproc --verbose to determine the source of the problem.

xsltproc bill_materials.xsl bill_materials.xml
Table 6. xsltproc results
Part nameNumber Required
Frame1
Jam1
Panels3
Window1
Strike Plate1
  • Tax Rate used to Calculate Total:6.00%
  • Total Supplier Cost for One Door:$349.80
  • Total Retail Cost for One Door:$418.70

Steps 4 and 5. Place the XSLT stylesheeet in the AppPage in an MIVAR variable and escape $, ' and < and > in xslt test functions. Also, place the XML document in an MIVAR variable.

Listing 37 is a truncated version of the AppPage with the changes for steps 4 and 5. This AppPage is saved as debug1AppPage.html in the xslt_udr_examples/documents directory.

NOTE: "\\" characters denote continuation of code to the next line.

Listing 37. debug1AppPage.html
<?MIVAR name=xsldoc><?xml version="1.0" ?> 
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/ \\
 Transform">
<xsl:output method="html" version="4.0" />

  <xsl:template match="/">
    <xsl:variable name="partList" select="/BOM/PART"/>
    <xsl:variable name="partCost" select="number(0)"/>
    <xsl:variable name="partRetCost" select="number(0)"/>
    <xsl:variable name="taxRate" select="number(1.06)"/>

    <table border="1">
    <xsl:call-template name="cost">

    .... several lines removed

        <xsl:otherwise>
        <tr><td>Tax Rate used to Calculate Total:<xsl:value-of \\ 
		  select="format-number(($$taxRate - 1.0) * 100,''#.00'')"  \\
		  />%</td></tr>
        <tr><td>Total <b>Supplier</b> Cost for One Door: \\
		 $$<xsl:value-of select="format-number($$partCost *  \\
		 $$taxRate,''#.00'')" /></td></tr>
        <tr><td>Total <b>Retail</b> Cost for One Door: \\
		 $$<xsl:value-of select="format-number($$partRetCost *  \\
		 $$taxRate,''#.00'')" /></td></tr>
        </xsl:otherwise>
      </xsl:choose>
  </xsl:template>
</xsl:stylesheet>
<?/MIVAR>
<?MIVAR name=xmldoc><?MISQL SQL="execute function FileToHtml \\
  ('/local0/xslt_udr_examples/documents/bill_materials.xml');" \\
   >$1<?/MISQL><?/MIVAR>

Step 6. Execute xslt_udr_AsHtml() using the two variables just created.

Below is the only addition to the AppPage above.

<?MISQL SQL="execute function xslt_udr_AsHtml('$xsldoc', '$xmldoc');">$1<?/MISQL>

Once this AppPage, xslt_udr_examples/documents/debug2AppPage.html, is inserted into the database and called from the Webdriver URL, the same results are displayed to the browser as when xsltproc was called. Our conversion from the static stylesheet to the AppPage has been successful!

Note: Prior to inserting the AppPage in your database, edit the path to the bill_materials.xml file in the MISQL statement to reflect your actual path.

So far we have not employed any debugging techniques or run into any problems. Now that we know the last version of our AppPage, debug2AppPage.html, is working, let's edit it to cause a few problems and see what they look like.

debug3AppPage.html has been changed on line 13 so the xslt variable partList has not been escaped with a second dollar sign. Below is the way this line looks before and after the change.

before - <xsl:with-param name="partList" select="$$partList" />
 after - <xsl:with-param name="partList" select="$partList" />

After inserting debug3AppPage.html into the database and executing it through Webdriver, the output to the browser appears like this:

Dynamic Page Generation Error: Undefined variable:$partList

This error is generated by the Web DataBlade and is reporting that the $partList variable has not been defined. This occurred because we did not define a Web DataBlade variable by that name, but instead attempted to create an XSLT variable by that name without using the dollar sign to escape the meaning of the other dollar sign.

Let's change debug2AppPage.html (which has $partList correctly defined) so that the quotes used in the format-number when displaying the tax rate are not escaped. Below is the line we are changing (NOTE: "\\" characters denote continuation of code to the next line):

before - <tr><td>Tax Rate used to Calculate Total:<xsl:value-of \\ 
  select="format-number(($$taxRate - 1.0) * 100,''#.00'')" />%</td></tr>
after - <tr><td>Tax Rate used to Calculate Total:<xsl:value-of \\ 
  select="format-number(($$taxRate - 1.0) * 100,'#.00')" />%</td></tr>

The change is saved to debug4AppPage.html. Here are the results when executing debug4AppPage.html:

Dynamic Page Generation Error: Syntax error or access violation

This is another Web DataBlade error explaining it can not execute the page properly due to a syntax error. In this case it is caused by the single quotes not being escaped.

Step 7. Where desired, substitute Web DataBlade variables in the stylesheet.

For this example we will modify debug2AppPage.html to be a little more flexible. Let's suppose our AppPage was part of a Web Application where customers were allowed to purchase our parts from different counties, and therefore the tax rates would be different. In this fictitious application the AppPage prior to our current AppPage allowed the customer to enter the appropriate tax rate. Our new AppPage, debug5AppPage.html has a new Web DataBlade variable added to accept the tax rate.

Only two changes are required to create our new AppPage from debug2AppPage. This line is added to the beginning:

<?MIVAR COND=$(NXST,$webTaxRate) name=$webTaxRate>1.06<?/MIVAR>

And this line which is part of the template where the match attribute is set to "/" is changed:

before - <xsl:variable name="taxRate" select="$$taxRate"/>
after  - <xsl:variable name="taxRate" select="<?MIVAR>$webTaxRate< 
        ?/MIVAR>"/>

The first change uses the Web DataBlade variable processing function, NXST. The statement checks for the existence of the $webTaxRate variable. If it does not exist it creates the variable and sets it to the value of 1.06. In our application we assume the AppPage prior to this page creates the $webTaxRate variable and sets it to the appropriate amount; however it is a good idea to check for its existence anyway.

The second change passes the value of the $webTaxRate Web DataBlade variable to the xslt variable $taxRate. The rest of the application where $taxRate is referenced does not need to be changed since the $taxRate variable has been initialized to the value of $webTaxRate.

Executing debug5AppPage.html using a URL similiar to the one below produces the same result as debug2AppPage.html.

http://www.myserver.com/xslt_demo/?MIval=/debug5AppPage.html

Since the variable $webTaxRate was not passed in as part of the query string for the above URL, $webTaxRate was set to the default of 1.06. The below URL, appropriate for Alameda County, California, sets the tax rate to 8.25 percent.

http://www.myserver.com/xslt_demo/?MIval=/debug5AppPage.html&webTaxRate=1.0825

The results for calling this URL are shown below:

Table 7
Part nameNumber Required
Frame1
Jam1
Panels3
Window1
Strike Plate1
  • Tax Rate used to Calculate Total:8.25%
  • Total Supplier Cost for One Door:$357.230
  • Total Retail Cost for One Door:$427.59

Step 8. If the results from executing xslt_udr_AsHtml() are not as expected, output the Web DataBlade variables holding the XML and XSL documents to check the results.

Let's say the application was designed with no validation for the input of the tax rate. If alpha characters were entered the AppPage would display the cost of the door like this:

Table 8
Part nameNumber Required
Frame1
Jam1
Panels3
Window1
Strike Plate1
  • Tax Rate used to Calculate Total:NaN%
  • Total Supplier Cost for One Door:$NaN
  • Total Retail Cost for One Door:$NaN

Although this example is simplified, it demonstrates a case where it is helpful to see what the XML document and XSL document are prior to being invoked by xslt_udr_AsHtml(). Adding the following line and removing the line that invokes xslt_udr_AsHtml() to our AppPage will allow us to see the contents of the $xsldoc Web DataBlade variable:

removed - <?MISQL SQL="execute function xslt_udr_AsHtml('$xsldoc', 
         '$xmldoc');">$1<?/MISQL>
added   - <?MIVAR>$xsldoc<?/MIVAR>

Calling debug6AppPage.html with the following URL will cause the problem of an invalid $webTaxRate:

http://www.myserver.com/xslt_demo/?MIval=/debug6AppPage.html&webTaxRate=hello

The results of calling the above URL via Internet Explorer displays as XML since the XSL document is well formed.

Listing 38. Results of calling URL via IE
<?xml version="1.0" ?> 
- <xsl:stylesheet version="1.0" 
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="html" version="4.0" /> 
- <xsl:template match="/">
  <xsl:variable name="partList" select="/BOM/PART" /> 
  <xsl:variable name="partCost" select="number(0)" /> 
  <xsl:variable name="partRetCost" select="number(0)" /> 
  <xsl:variable name="taxRate" select="hello" /> 
- <table border="1">
- <xsl:call-template name="cost">
  <xsl:with-param name="partList" select="$partList" /> 
  <xsl:with-param name="partCost" select="$partCost" /> 
  <xsl:with-param name="partRetCost" select="$partRetCost" /> 
  <xsl:with-param name="taxRate" select="$taxRate" /> 
  </xsl:call-template>
... the rest of the output has been removed

The problem with this stylesheet is readily apparent when looking at the XSL document. In the template where the match attribute is set to "/", the XSL variable taxRate has a select value of 'hello' which causes any calculations involving the $taxRate variable to report NaN.

Step 9. Use xslt_TraceSet with a value of 35 to see the values of the XML and XSL documents being sent to xslt_udr.

Let's use the same problem we had above with our Web DataBlade variable not being initialized correctly, but instead of using the methodology in 8 above, use xslt_TraceSet. Only one new line is added to debug5AppPage.html to create debug7AppPage.html which executes the procedure xslt_TraceSet prior to calling xslt_udr_AsHtml().

line added before xslt_udr_AsHtml 
<?MISQL SQL="execute procedure xslt_TraceSet('/tmp/xslt_trace.out', 35);"><?/MISQL>

The xslt_TraceSet UDR takes two parameters - the full path to the output file of the trace results, and the level of tracing. Refer to the article on alphaWorks, IBM Informix XSLT DataBlade for details of the various tracing levels. A trace level of 35 outputs the stylesheet and document arguments before XML/XSLT processing occurs.

The output below shows partial results of the trace file, /tmp/xslt_trace.out, from calling this URL:

Listing 39. /tmp/xslt_trace.out
http://www.myserver.com/xslt_demo/?MIval=/debug7AppPage.html&webTaxRate=goodbye

============================================================

Tracing session: 936 on 01/29/2003

12:55:20  ixslt_udr: entering function.
12:55:20  ixslt_dispatch_main: entering function.
12:55:20  ixslt_xml_setup(0): entering function.
12:55:20  ixslt_xml_setup(0): xml memory setup.
12:55:20  ixslt_xml_setup(0): error handling set up.
12:55:20  ixslt_xml_setup(0): parser initialized.
12:55:20  ixslt_xml_setup(0): leaving function.

... output removed

12:55:20  ixslt_eng(0): >>>>>>>>>>>BEGIN STYLESHEET >>>>>>>>>>>
<?xml version="1.0" ?> 
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/
Transform">
<xsl:output method="html" version="4.0" />

  <xsl:template match="/">
    <xsl:variable name="partList" select="/BOM/PART"/>
    <xsl:variable name="partCost" select="number(0)"/>
    <xsl:variable name="partRetCost" select="number(0)"/>
    <xsl:variable name="taxRate" select="goodbye"/>

    <table border="1">
    <xsl:call-template name="cost">
        <xsl:with-param name="partList" select="$partList" />
        <xsl:with-param name="partCost" select="$partCost" />
        <xsl:with-param name="partRetCost" select="$partRetCost" />
        <xsl:with-param name="taxRate" select="$taxRate"/>

... output removed

        <tr><td>Total <b>Supplier</b> 
		  Cost for One Door:$<xsl:value-of select="format-number
		  ($partCost * $taxRate,'#.00')" /></td></tr>
        <tr><td>Total <b>Retail</b> 
		  Cost for One Door:$<xsl:value-of select="format-number
		  ($partRetCost * $taxRate,'#.00')" /></t
12:55:20  ixslt_eng(0): <<<<<<<<<<<<<<<<< END STYLESHEET <<<<<<<<<<<<<
12:55:20  ixslt_eng(0): >>>>>>>>>> BEGIN DOCUMENT >>>>>>>>>>>
<?xml version="1.0"?>
<BOM NUM="3267" NAME="Door">
  <PART>
    <ID>36</ID>
    <NAME>Frame</NAME>
    <COST>110.00</COST>
    <MARKUP>1.25</MARKUP>
    <NUM_REQD>1</NUM_REQD>
  </PART>

... output removed

    <NAME>Strike Plate</NAME>
    <COST>20.00</COST>
    <MARKUP>1.05</MARKUP>
    <NUM_REQD>1</NUM_REQD>
  </PART>
</BOM>


12:55:20  ixslt_eng(0): <<<<<<<<<<<<<<<<< END DOCUMENT <<<<<<<<<<<<<<<
12:55:20  ixslt_eng(0): style sheet loaded as XML.
12:55:20  ixslt_eng(0): style sheet parsed.
12:55:20  ixslt_eng(0): document parsed.
12:55:20  ixslt_eng(0): calling xsltApplyStylesheet.
12:55:20  ixslt_eng(0): xsltApplyStylesheet succeeded.
12:55:20  ixslt_eng(0): xsltSaveResultToString retcode=0, return value size=643.
12:55:20  ixslt_eng(0): leaving function.
12:55:20  ixslt_dispatch_main: calling xmlCleanupParser after success
12:55:20  ixslt_dispatch_main: leaving function.
12:55:20  ixslt_udr: leaving function.

As in the previous example, the xslt taxRate variable is set to 'goodbye', instead of a numeric value as required.

xslt_udr Options and xslt_traceSet

xslt_udr supports a subset of options supported by xsltproc. For detailed information about which are supported see the section, xsltproc options, in the alphaWorks article, IBM Informix XSLT DataBlade. Two of the most important options are verbose and maxdepth.

The verbose option shows the XML and XSLT processing and displays the values of user functions and variables. Although you should debug stylesheets using xsltproc prior to executing xslt_udr, there may be times when the verbose option of xslt_udr is helpful.

Before executing xslt_udr with the verbose option, the xslt_TraceSet UDR must be set to a level greater than or equal to 20.

The most commonly used settings for the xslt_TraceSet level are 20 and 35. Setting the level to 20 is roughly equivalent to xsltproc's verbose. Setting the level to 35 and calling xslt_udr with verbose provides the verbose output, as well as the incoming XML document and incoming stylesheet.

Listing 40 shows the output from the execution of the following SQL:

execute procedure xslt_TraceSet('/tmp/xslt_trace2.out', 20);

select xslt_udr(s.style_doc, x.xml_doc, '--verbose') 
FROM style_sheets s, xml_docs x
WHERE s.style_title = 'ecommerce_ecomm.xsl' AND x.xml_title = 'ecommerce.xml';
Listing 40. xslt_trace2.out
%cat /tmp/xslt_trace2.out
============================================================


Tracing session: 1130 on 02/05/2003

13:17:54  ixslt_verbose: Added namespace: xsl mapped to http://www.w3.org/199
/XSL/Transform

13:17:54  ixslt_verbose: xsltPrecomputeStylesheet: removing ignorable blank
 node

13:17:54  ixslt_verbose: xsltParseStylesheetProcess : found stylesheet

13:17:54  ixslt_verbose: xsltCompilePattern : parsing '/'

13:17:54  ixslt_verbose: xsltCompilePattern : parsed /, default priority 
0.500000

13:17:54  ixslt_verbose: added pattern : '/' priority 0.500000

13:17:54  ixslt_verbose: parsed 2 templates

13:17:54  ixslt_verbose: Resolving attribute sets references

13:17:54  ixslt_verbose: Registered 0 modules

13:17:54  ixslt_verbose: Registering global variables

13:17:54  ixslt_verbose: xsltProcessOneNode: applying template '/' for /

13:17:54  ixslt_verbose: Registering variable total_ecomm

13:17:54  ixslt_verbose: Building variable total_ecomm
13:17:54  ixslt_verbose:  select sum(//ecomm)
13:17:54  ixslt_verbose: 

13:17:54  ixslt_verbose: Evaluating variable total_ecomm

13:17:54  ixslt_verbose: Registering variable rowList

13:17:54  ixslt_verbose: Building variable rowList
13:17:54  ixslt_verbose:  select /TABLE/ROW
13:17:54  ixslt_verbose: 

13:17:54  ixslt_verbose: Evaluating variable rowList

13:17:54  ixslt_verbose: call-template: name percent_ecomm

13:17:54  ixslt_verbose: Handling param total_ecomm

... output removed

13:17:55  ixslt_verbose: found variable rowList

13:17:55  ixslt_verbose: xsltChoose: test evaluate to 1

13:17:55  ixslt_verbose: xsltIf: test $rowList[1]/ecomm > 20000

13:17:55  ixslt_verbose: Lookup variable rowList

13:17:55  ixslt_verbose: found variable rowList

13:17:55  ixslt_verbose: xsltIf: test evaluate to 1

... output removed


13:17:59  ixslt_verbose: call-template returned: name percent_ecomm

13:17:59  ixslt_verbose: call-template returned: name percent_ecomm

13:17:59  ixslt_verbose: call-template returned: name percent_ecomm

13:18:00  ixslt_verbose: call-template returned: name percent_ecomm

13:18:00  ixslt_verbose: call-template returned: name percent_ecomm

13:18:00  ixslt_verbose: call-template returned: name percent_ecomm

13:18:00  ixslt_verbose: call-template returned: name percent_ecomm

13:18:00  ixslt_verbose: call-template returned: name percent_ecomm

13:18:00  ixslt_verbose: call-template returned: name percent_ecomm

13:18:00  ixslt_verbose: call-template returned: name percent_ecomm

13:18:00  ixslt_verbose: call-template returned: name percent_ecomm

13:18:00  ixslt_verbose: call-template returned: name percent_ecomm

Another important option is the maxdepth option. The default maxdepth for xslt_udr is 50. This restricts the depth of recursive calls substantially more than xsltproc which, by default, is 5000.

Prior to increasing the maxdepth value for xslt_udr, test the stylesheet using xsltproc with a maxdepth of 50 to verify that the problem is due to the recursion depth. Incrementally increase the value of maxdepth for xsltproc to find an approximate value. Then increase the value for xslt_udr to this level and adjust slowly upward if necessary. Test the changes prior to deploying an application. A more thorough explanation of the recursion restrictions in the Informix environment are available in the alphaWorks article IBM Informix XSLT DataBlade, in the section, "Recursion depth is severely restricted".

Web DataBlade tracing

There may be cases where a problem occurs that is unrelated to xslt_udr or the stylesheet but is Web DataBlade related. To enable WebExplode tracing, refer to the chapter entitled 'Debugging Web DataBlade Module Applications' in the IBM Informix Web DataBlade Module Application Developer's Guide, V4.13, IBM

Two variables, MI_WEBEXPLEVEL and MI_WEBEXPLOG, must be set using the Web DataBlade Administration Tool to enable WebExplode tracing. There are numerous values for the MI_WEBEXPLEVEL trace settings which output variable expansion, SQL statements being executed and processing of UDR's to name a few. Additionally there are 3 levels of granularity to set for the trace output.

The example below shows the output to the trace file specified by MI_WEBEXPLOG when setting the value of MI_WEBEXPLEVEL to a value of 92. The Webdriver URL called to generate this output is:

http://www.myserver.com/xslt_demo/?MIval=/dynEcommAppPage.html

Listing 41. Output to trace file
% cat /tmp/webexplode.log

============================================================

Tracing session: 1153 on 02/05/2003

15:29:08  1153     VAR (VarTag) enter  name="xmldoc"><?MISQL 
SQL="select code, desc, eco
15:29:08  1153     Sql Tag: orig:<?MISQL  SQL="select code, desc, ecomm, 
total from ecommerce;" XMLDOC="TABLE">$1<?/MISQL>
15:29:08  1153     Sql Tag exec :  select code, desc, ecomm, total from ecommerce;
15:29:08  1153     Sql Tag: exiting
15:29:08  1153    VAR (VarTag) exiting
15:29:08  1153     VAR (VarTag) enter  name="xsl_doc"><?xml version="1.0" 
?> <xsl:styl
15:29:08  1153     VAR (VarTag) enter >$(WEBUNHTML,">")<?/MIVAR>
15:29:08  1153       Diesel: $(WEBUNHTML,">")
15:29:08  1153     Diesel: exiting
15:29:08  1153     VAR (VarTag) exiting
15:29:08  1153    VAR (VarTag) exiting
15:29:08  1153     Sql Tag: orig:<?MISQL  SQL="execute function 
xslt_udr_AsHtml('$xsl_doc', '$xmldoc');">$1<?/MISQL>

15:29:08  1153     Sql Tag exec :  execute function xslt_udr_AsHtml
('<?xml version="1.0" ?> 
 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/
 Transform">
 <xsl:output method="html" version="4.0" />

 <xsl:template match="/">
   <xsl:variable name="total_ecomm" select="sum(//ecomm)"/>
   <xsl:variable name="rowList" select="/TABLE/ROW"/>
   <table>
   <xsl:call-template name="percent_ecomm">
        <xsl:with-param name="total_ecomm" select=
		"$total_ecomm" />
        <xsl:with-param name="rowList" select="$rowList" />
   </xsl:call-template>
    </table>
 </xsl:template>

 <xsl:template name="percent_ecomm">
    <xsl:param name="total_ecomm" />
    <xsl:param name="rowList" />
    <xsl:choose>
       <xsl:when test="$rowList">
         <xsl:if test="$rowList[1]/ecomm>20000">
             <tr>
          
15:29:08  1153     Sql Tag: exiting

When debugging Web DataBlade AppPages where WebExplode tracing is enabled be aware of the effect of calling xslt_TraceSet from within the AppPage. The tracing output from executing the page will first go to the file specified by MI_WEBEXPLOG, then once xslt_TraceSet is called both the WebExplode tracing and the xslt_TraceSet tracing will go to the file specified by xslt_TraceSet.


Using tools to generate XSL stylesheets

Some of the available XML Tools on the market allow for creating XSL stylesheets and debugging them. One tool, which is a complete IDE, is XMLSpy5. XML editing and validation plus XSL Editing and Debugging are some of the nice features of this commercial product.

A good article on XML editors can be found at the IBM developerWorks site entitled, XML Matters: A roundup of editors.


Conclusion

This article showed how to use the XSLT and Web DataBlades to generate, transform and consume XML. Now it's up to you to try it out. Download the samples, and try using the Web DataBlade to generate XML, or the XSLT DataBlade to transform XML using XSLT stylesheets from within dbaccess or a Web DataBlade Application Page.


Download

DescriptionNameSize
Download file xslt_udr_examples.tar.Zxslt_udr_examples.tar.Z  ( HTTP | FTP )115 KB

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=87799
ArticleTitle=Using the IBM Informix XSLT and Web DataBlades
publish-date=08282001