17.10 - Example: XSLT_SHRED_BATCH <xsl:param> - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - XML Data Type

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

This example shows how to use the <xsl:param> parameter to define context level constants.

The following shows the syntax for <xsl:param>.

<xsl:param name=”XML_TimeStamp”  select="date:date-time()"></xsl:param>

Before running the example, ensure the test user is created with the required permissions and the required tables are created. See Setting Up the XSLT_SHRED_BATCH and XSLT_SHRED Examples for details.

Populate the staging table used to store the XML source document.

DELETE Input_Docs;

INSERT INTO Input_Docs(1, CREATEXML('<?xml version="1.0"?>

Call the XSLT_SHRED_BATCH stored procedure with the stylesheet mapping to shred the XML data stored in the staging input table. The following arguments are used for the call.

  • The queryString is: sel * from xsltuser.Input_Docs
  • The xsltMapping argument is supplied by invoking the CREATEXML function with a stylesheet as input. This stylesheet will be applied to the XML document.
  • The externalContext sets the defaultDatabase to xsltuser.
  • The resultCode is returned in :res. A successful operation returns 0.

This example shows the <xsl:param> being set to a timestamp and then being used as a value for one of the columns in the target table.

CALL TD_SYSXML.XSLT_SHRED_BATCH('sel * from xsltuser.Input_Docs', CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:date="http://exslt.org/dates-and-times" extension-element-prefixes="date" version="1.0">
<xsl:param name="XML_TimeStamp"  select="date:date-time()"></xsl:param>
<xsl:template match="/Root">
     <xsl:for-each select="predictixOfferMessage">
       <offerid> <xsl:value-of select="Offer/offerid"/></offerid>
        <xsl:value-of select="mediaBlock/mediaBlockid"/>
       <datetimecol default="{$XML_TimeStamp}">
        <xsl:value-of select="Offer/dummyelement"/>
</xsl:stylesheet>'), 'defaultDatabase=xsltuser', :res);
Result: To view the updated data in the target table, run: SELECT * FROM Offer;
offerid     mediaBlockid                             datetimecol
----------  ---------------------------- ------------------------------
1000001     90000000010001               2014-11-04T15:22:55-08:00
1000002     90000000010002               2014-11-04T15:22:55-08:00