17.10 - Example: XSLT_SHRED Using genexp - 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 genexp declaration, which is required for XSLT_SHRED, unlike XSLT_SHRED_BATCH.

The Primary Index in the following example is not used as a key for the update.

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 DefaultValue3(C_id, C_Byteintc) values(1,1);

Call the XSLT_SHRED stored procedure to shred the XML document. The following arguments are used for the call.

  • The xmlDoc is created automatically in this example with the NEW XML statement.
  • 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.
  • The resultCode is returned in :res. A successful operation returns 0.
</AllTypes></Root>'), 	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="/Root">
    <xsltuser.DefaultValue3 pkey="C_Byteintc">
     <xsl:for-each select="AllTypes">
        <C_Id genexp="cast(? as int)" default="10">
           <xsl:value-of select="Numeric/Bytec"/>
        <C_Byteintc genexp="cast(? as byteint)">
           <xsl:value-of select="Numeric/Byteintc"/>
        <C_Smallintc genexp="cast(? as smallint)" >
           <xsl:value-of select="Numeric/Smallintc"/>
        <C_Intc genexp="cast(? as integer)">
           <xsl:value-of select="Numeric/Intc"/>
</xsl:stylesheet>'), '',:res);

Result: To view the updated data in the target table, run:

SELECT * FROM DefaultValue3;

       C_Id  C_Byteintc  C_Smallintc       C_Intc
-----------  ----------  -----------  -----------
         10           1          200          200