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

Teradata Vantage™ XML Data Type

Teradata Database
Teradata Vantage NewSQL Engine
March 2019
Programming Reference

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