17.10 - Example: Using Stylesheets in XSLT Transformations - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - XML Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1140-171K
Language
English (United States)

You can store stylesheets in tables and use them with functions and methods that perform XSLT transformations through joins. In this example, the xslt.txt file contains information for importing the stylesheet (the stylesheet file name and ID). The stylesheet to be stored in the styletab table is written in the Itemlist.xslt file.

CREATE TABLE styletab(
   stylesheetid INTEGER,
   stylesheetcontent XML )
   PRIMARY INDEX (stylesheetid);

The xslt.txt file contains:

Itemlist.xslt|1

The Itemlist.xslt file contains:

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:template match="/Customer">
      <html>
      <title>Items Ordered</title>
      <body>
      <table border="1">
      <tr>
         <th>Item ID</th>
         <th>Description</th>
         <th>Part Number</th>
         <th>Qty</th>
         <th>Unit Price</th>
         <th>Total Price</th>
      </tr>
      <xsl:for-each select="Order/Item">
      <tr>
         <td><xsl:value-of select="@ID"/></td>
         <td><xsl:value-of select="Description"/></td>
         <td><xsl:value-of select="PartNumber"/></td>
         <td><xsl:value-of select="Quantity"/></td>
         <td><xsl:value-of select="UnitPrice"/></td>
         <td><xsl:value-of select="Price"/></td>
      </tr>
      </xsl:for-each>
      </table>
      </body>
      </html>
   </xsl:template>
</xsl:stylesheet>

Use this statement to store the stylesheet in the styletab table:

.IMPORT VARTEXT '|' LOBCOLS=1 FILE='xslt.txt'
USING (fxslt CLOB AS DEFERRED, schemaid VARCHAR(32))
INSERT INTO styletab values(:schemaid, new XML(:fxslt));

The XSLTTRANSFORM method can now reference the stylesheet by joining the styletab table:

SELECT customerXML.XSLTTRANSFORM(styletab.stylesheetcontent, '')
FROM customer, styletab
WHERE customerID=1 and styletab.stylesheetid = 1;

Here are partial results from the query. The ellipsis (...) is not part of the query results. It indicates that the query returns additional results that are truncated in the example.

customerXML.XSLTTRANSFORM(stylesheetcontent, '')
-----------------------------------------------------------------------
<html><title>Items Ordered</title><body><table border="1"><tr><th>Item ID</th><th>Description</th><th>Part Number</th><th>Qty</th><th>Unit Price</th><th>Total Price</th></tr><tr><td>001</td><td>Motoro ...