Example: XSLT_SHRED_BATCH with a Transient SQL Expression - Teradata Vantage - Analytics Database

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.

<Row>
      <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 >
           .
           .
           .
 </Row>

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"?> 
<Customers>
 <Customer>  
  <C_Id>100</C_Id> 
  <C_Name>EMP_ABCD</C_Name>
  <C_Sal_Transient>1200</C_Sal_Transient>
</Customer>
<Customer> 
  <C_Id>200</C_Id>
  <C_Name>EMP_XYZ</C_Name>
  <C_Sal_Transient>1300</C_Sal_Transient>
</Customer>
</Customers>'));

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">
 <Transaction>
  <Insert>
   <Table>
    <xsltuser.TransientTbl>
     <xsl:for-each select="Customer">
      <Row>
       <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_Transient>
       <C_Sal sqlexpr="true">100 * C_Sal_Transient</C_Sal>
      </Row>
     </xsl:for-each>
    </xsltuser.TransientTbl>
   </Table>
  </Insert>
 </Transaction>
</xsl:template>
</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