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.
<?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>
- 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 ~
<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.
<xsl:param name="showUniversity"/>
<xsl:param name="headline"/>
- 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 ~
<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>