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
July 2021
Last Update
Content Type
Programming Reference
Publication ID
English (United States)
Last Update

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