DB2 Version 9.7 for Linux, UNIX, and Windows

XSLTRANSFORM scalar function

Use XSLTRANSFORM to convert XML data into other formats, including the conversion of XML documents that conform to one XML schema into documents that conform to another schema.


>>-XSLTRANSFORM------------------------------------------------->

                                                                     .-AS CLOB(2G)-------.      
>--(--xml-document--USING--xsl-stylesheet--+----------------------+--+-------------------+--)-><
                                           '-WITH--xsl-parameters-'  '-AS--| data-type |-'      

data-type

                    .-(-- -1--)-----.                              
|--+-+-CHARACTER-+--+---------------+--------------------------+--|
   | '-CHAR------'  '-(--integer--)-'                          |   
   +-+-VARCHAR----------------+--(--integer--)-----------------+   
   | '-+-CHARACTER-+--VARYING-'                                |   
   |   '-CHAR------'                                           |   
   |                                  .-(--1M--)-------------. |   
   +-+-CLOB------------------------+--+----------------------+-+   
   | '-+-CHARACTER-+--LARGE OBJECT-'  '-(--integer--+---+--)-' |   
   |   '-CHAR------'                                +-K-+      |   
   |                                                +-M-+      |   
   |                                                '-G-'      |   
   |                          .-(--1M--)-------------.         |   
   '-+-BLOB----------------+--+----------------------+---------'   
     '-BINARY LARGE OBJECT-'  '-(--integer--+---+--)-'             
                                            +-K-+                  
                                            +-M-+                  
                                            '-G-'

The schema is SYSIBM. This function cannot be specified as a qualified name.

The XSLTRANSFORM function transforms an XML document into a different data format. The data can be transformed into any form possible for the XSLT processor, including but not limited to XML, HTML, or plain text.

All paths used by XSLTRANSFORM are internal to the database system. This command cannot currently be used directly with files or stylesheets residing in an external file system.

xml-document

An expression that returns a well-formed XML document with a data type of XML, CHAR, VARCHAR, CLOB, or BLOB. This is the document that is transformed using the XSL style sheet specified in xsl-stylesheet.

Note:

The XML document must at minimum be single-rooted and well-formed.

xsl-stylesheet

An expression that returns a well-formed XML document with a data type of XML, CHAR, VARCHAR, CLOB, or BLOB. The document is an XSL style sheet that conforms to the W3C XSLT Version 1.0 Recommendation. Style sheets incorporating XQUERY statements or the xsl:include declaration are not supported. This stylesheet is applied to transform the value specified in xml-document.

xsl-parameters

An expression that returns a well-formed XML document or null with a data type of XML, CHAR, VARCHAR, CLOB, or BLOB. This is a document that provides parameter values to the XSL stylesheet specified in xsl-stylesheet. The value of the parameter can be specified as an attribute, or as a text node.

The syntax of the parameter document is as follows:

<params xmlns="http://www.ibm.com/XSLTransformParameters">
<param name="..." value="..."/>
<param name="...">enter value here</param>
		...
</params>

Note:

The stylesheet document must have xsl:param element(s) in it with name attribute values that match the ones specified in the parameter document.

AS data-type

Specifies the result data type. The implicit or explicit length attribute of the specified result data type must be sufficient to contain the transformed output (SQLSTATE 22001). The default result data type is CLOB(2G).

Note:

If either the xml-document argument or the xsl-stylesheet argument is null, the result will be null.

Code page conversion might occur when storing any of the above documents in a CHAR, VARCHAR, or CLOB column, which might result in a character loss.

Example

This example illustrates how to use XSLT as a formatting engine. To get set up, first insert the two example documents below into the database.

INSERT INTO XML_TAB VALUES        
(1,
          '<?xml version="1.0"?>
<students xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xsi:noNamespaceSchemaLocation = "/home/steffen/xsd/xslt.xsd">
<student studentID="1" firstName="Steffen" lastName="Siegmund" 
    age=â€23â€ university=â€Rostockâ€/>
</students>',
    '<?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">First Name</td>
                                <td width="200">Last 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="@firstName"/></td>
                  <td><xsl:value-of select="@lastName"/></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>'
);

Next, call the XSLTRANSFORM function to convert the XML data into HTML and display it.

SELECT XSLTRANSFORM (XML_DOC USING XSL_DOC AS CLOB(1M)) FROM XML_TAB;

The result is this document:

<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">First Name</td>
<td width="200">Last Name</td>
<td width="50">Age</td>
</tr>
</th>
 <tr>
<td>1</td>
<td>Steffen</td><td>Siegmund</td>
<td>23</td>
</tr>
              </table>
</body>
</html>

In this example, the output is HTML and the parameters influence only what HTML is produced and what data is brought over to it. As such it illustrates the use of XSLT as a formatting engine for end-user output.

Usage note:

There are many methods you can use to transform XML documents including the XSLTRANSFORM function, an XQuery update expression, and XSLT processing by an external application server. For documents stored in a DB2® XML column, many transformations can be performed more efficiently by using an XQuery update expression rather than with XSLT because XSLT always requires parsing of the XML documents that are being transformed. If you decide to transform XML documents with XSLT, you should make careful decisions about whether to transform the document in the database or in an application server.