Using the IBM Informix XSLT and Web DataBlades

Document options Document options requiring JavaScript are not displayed Sample code Rate this page Help us improve this content

Level: Introductory

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

28 Aug 2001

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.

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.

Back to top

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.

<?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.

<?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.

Back to top

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

Back to top

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

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:

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:

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.

Back to top

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

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.

Back to top

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.

Back to top

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:

<?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.

<?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.

Back to top

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:

<?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.

<?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:

<?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.

<?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

<?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.

Back to top

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:

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

(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

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';

(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

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';

(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.

Back to top

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

Listing 18 shows the HTML-formatted data as displayed through a browser:

Listing 18. HTML-formatted data

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 19 shows the results of executing the SQL in the xslt_udr_examples/sql/bad_insert.sql file:

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.

Back to top

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.

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 21 below shows the output from applying incorrect1.xsl to order.xml from xsltproc.

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

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.

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" />

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.

Back to top

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 3 shows some differences between creating static stylesheets and stylesheets created in a Web DataBlade AppPage.

Table 3. Static stylesheets vs. Web Datablade stylesheets

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 24 below shows ecommerce_html.xsl:

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

<?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 25 are not part of the stylesheet but included for explanatory purposes.

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

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.

Back to top

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 26:

<?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.

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 28:

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

<?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" />