DB2 Version 9.7 for Linux, UNIX, and Windows

Lesson 8: Transforming with XSLT stylesheets

This lesson shows you how to use an Extensible Stylesheet Language Transformation (XSLT) stylesheet and the XSLTRANSFORM built-in function to convert XML data in a database into other formats.

Consider an XML document that contains an arbitrary number of university student records. Each student element contains a student's ID, given name, family name, age, and university that the student attends. The following document contains two students:

<?xml version="1.0"?>   
<students xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
     <student studentID="1" givenName="Steffen" familyName="Siegmund" 
	       age="21" university="Rostock"/> 
     <student studentID="2" givenName="Helena" familyName="Schmidt" 
	        age="23" university="Rostock"/>
</students>

Also, assume that you want to extract the information in the XML records and create an HTML Web page that you can view in a browser. To transform the information, you require the following XSLT stylesheet:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
     xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:param name="headline"/>
<xsl:param name="showUniversity"/>
<xsl:template match="students">    
     <html>
     <head/>
     <body>
     <h1><xsl:value-of select="$headline"/></h1>
     <table border="1">
     <th>
     <tr>
     <td width="80">StudentID</td>
     <td width="200">Given Name</td>
     <td width="200">Family Name</td>
     <td width="50">Age</td>
     <xsl:choose>
          <xsl:when test="$showUniversity =''true''">
               <td width="200">University</td>
          </xsl:when>
     </xsl:choose>
     </tr>
     </th>
     <xsl:apply-templates/>
     </table>
     </body>
     </html>
     </xsl:template>
          <xsl:template match="student">
               <tr>
               <td><xsl:value-of select="@studentID"/></td>
               <td><xsl:value-of select="@givenName"/></td>
               <td><xsl:value-of select="@familyName"/></td>
               <td><xsl:value-of select="@age"/></td>
               <xsl:choose>
                    <xsl:when test="$showUniversity = ''true'' ">
                         <td><xsl:value-of select="@university"/></td>
                    </xsl:when>
               </xsl:choose>
               </tr>
     </xsl:template>
</xsl:stylesheet>

To transform the data:

Create two tables into which you store the XML document and stylesheet document by running the following commands:
```
CREATE TABLE XML_DATA (DOCID INTEGER, XML_DOC XML )~
CREATE TABLE XML_TRANS (XSLID INTEGER, XSLT_DOC CLOB(1M))~
```

Insert the XML document and entire XSLT stylesheet into the tables by using the following INSERT statements.

For brevity, a truncated version of the XSLT stylesheet is shown in second INSERT statement in this step. Replace truncated stylesheet with the XSLT stylesheet listed previously before using the statement.

INSERT INTO XML_DATA VALUES
     (1,
      '<?xml version="1.0"?>
       <students xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
          <student studentID="1" givenName="Steffen" familyName="Siegmund" 
	           age="21" university="Rostock"/> 
          <student studentID="2" givenName="Helena" familyName="Schmidt" 
	          age="23" university="Rostock"/>
      </students>'
 )~

INSERT INTO XML_TRANS VALUES
     (1,
     '<?xml version="1.0" encoding="UTF-8"?>
     <xsl:stylesheet version="1.0"
          ...
     </xsl:stylesheet>'
)~

Transform the XML document by calling the XSLTRANSFORM function:

SELECT XSLTRANSFORM (XML_DOC USING XSLT_DOC AS CLOB(1M)) 
   FROM XML_DATA, XML_TRANS WHERE DOCID = 1 and XSLID = 1 ~

The output of the transformation is the following HTML file:

<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<h1></h1>
<table border="1">
<th>
<tr>
<td width="80">StudentID</td>
<td width="200">Given Name</td>
<td width="200">Family Name</td>
<td width="50">Age</td>
</tr>
</th>
<tr>
<td>1</td>
<td>Steffen</td><td>Siegmund</td>
<td>21</td>
</tr>
<tr>
<td>2</td><td>Helena</td><td>Schmidt</td>
<td>23</td>
</tr>
</table>
</body>
</html>

You might want to alter the behavior of the XSLT stylesheet at run time, either to add information not contained in the XML records or to change the nature of the output itself (to create XHTML output instead of standard HTML output, for example). To alter the behavior, you can pass parameters to the XSLT process by using a parameter file. The parameter file is itself an XML document and contains param statements that correspond to similar statements in the XSLT stylesheet file.

Consider the following two parameters that are defined in the stylesheet but were not used in the previous transform:

<xsl:param name="showUniversity"/>
<xsl:param name="headline"/>

To transform the XML document using these parameters we store a parameters file in a table and use the file with the XSLTRANSFORM function.

Create the table PARAM_TAB to store the parameters file:

CREATE TABLE PARAM_TAB (DOCID INTEGER, PARAM VARCHAR(1000))~

Create a parameter file as follows:

INSERT INTO PARAM_TAB VALUES
     (1,
     '<?xml version="1.0"?>
     <params xmlns="http://www.ibm.com/XSLTransformParameters">
          <param name="showUniversity" value="true"/>
          <param name="headline">The student list</param>
     </params>'
     )~

Transform the XML document by calling the XSLTRANSFORM function:

SELECT XSLTRANSFORM (XML_DOC USING XSLT_DOC WITH PARAM AS CLOB(1M) ) 
   FROM XML_DATA X , PARAM_TAB P, XML_TRANS 
   WHERE X.DOCID=P.DOCID and XSLID = 1 ~

The output of this process is the following HTML file:

<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<h1>The student list</h1>
<table border="1">
<th>
<tr>
<td width="80">StudentID</td>
<td width="200">Given Name</td>
<td width="200">Family Name</td>
<td width="50">Age</td>
<td width="200">University</td>
</tr>
</th>
<tr>
<td>1</td>
<td>Steffen</td><td>Siegmund</td>
<td>21</td>
<td>Rostock</td>
</tr>
<tr>
<td>2</td>
<td>Helena</td><td>Schmidt</td>
<td>23</td>
<td>Rostock</td>
</tr>
</table>
</body>
</html>

Return to the tutorial