16.20 - Example: XSLT_SHRED_BATCH with a Transient SQL Expression - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ XML Data Type

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
Release Date
March 2019
Content Type
Programming Reference
Publication ID
English (United States)

This example shows how to use the <xsl:value-of select=""> mapping definition with a transient column. The value in the transient column is used to compute the value of a column in the target table. Any valid SQL expression can be used to do this computation, including user-defined functions when highly customized computations are required. The associated data item itself will not be inserted into the target table.

The following syntax shows the C_Name_Transient column being used in the computation of the C_Name column.

All transient columns must have an sqltype attribute with a valid SQL data type because this column does not exist in the target table.
      <C_Id><xsl:value-of select="/Id " /></C_Id> 
      <C_Name_Transient sqltype=”varchar(30)” transient=”true”>
                 <xsl:value-of select=" …../NameTransent " />
      </ C_Name_Transient >
      < C_Name sqlexpr=”true”><![CDATA[‘HYD_’ || C_Name_Transient]]> 
      < C_Name >

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.

INSERT INTO TransientTbl_Docs values(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.TransientTbl_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 is NULL for this example.
  • The resultCode is returned in :res. A successful operation returns 0.
CALL TD_SYSXML.XSLT_SHRED_BATCH('sel * from xsltuser.TransientTbl_Docs', 
CREATEXML('<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:template match="/Customers">
     <xsl:for-each select="Customer">
       <xsl:copy-of select="C_Id"/>
       <xsl:copy-of select="C_Name"/>
       <C_Sal_Transient sqltype="Int" transient="true">
         <xsl:value-of select="C_Sal_Transient" />
       <C_Sal sqlexpr="true">100 * C_Sal_Transient</C_Sal>
</xsl:stylesheet>'), NULL, :res);

Result: To view the updated data in the target table, run: SEL * FROM TransientTbl;

  C_Id  C_Name             C_Sal
------  ------------ -----------
   200  EMP_XYZ           130000
   100  EMP_ABCD          120000