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

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;
DELETE Offer;

INSERT INTO Input_Docs(1, CREATEXML('<?xml version="1.0"?>
<Root>
 <predictixOfferMessage>
  <Offer><offerid>1000001</offerid></Offer>
  <mediaBlock><mediaBlockid>90000000010001</mediaBlockid></mediaBlock>
 </predictixOfferMessage>
 <predictixOfferMessage>
  <Offer><offerid>1000002</offerid></Offer>
  <mediaBlock><mediaBlockid>90000000010002</mediaBlockid></mediaBlock>
 </predictixOfferMessage>
</Root>'));

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">
 <Transaction>
  <Insert>
   <Table>
    <Offer>
     <xsl:for-each select="predictixOfferMessage">
      <Row>
       <offerid> <xsl:value-of select="Offer/offerid"/></offerid>
       <mediaBlockid>
        <xsl:value-of select="mediaBlock/mediaBlockid"/>
       </mediaBlockid>
       <datetimecol default="{$XML_TimeStamp}">
        <xsl:value-of select="Offer/dummyelement"/>
       </datetimecol>
      </Row>
     </xsl:for-each>
    </Offer>
   </Table>
  </Insert>
 </Transaction>
 </xsl:template>
</xsl:stylesheet>'), 'defaultDatabase=xsltuser', :res);

offerid     mediaBlockid                             datetimecol
----------  ---------------------------- ------------------------------
1000001     90000000010001               2014-11-04T15:22:55-08:00
1000002     90000000010002               2014-11-04T15:22:55-08:00