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.
CALL TD_SYSXML.XSLT_SHRED(NEW XML('<Root>
<AllTypes>
<Numeric>
<Byteintc>1</Byteintc>
<Smallintc>200</Smallintc>
<Intc>200</Intc>
<Floatc>200</Floatc>
<Decimalc>200</Decimalc>
<Numberc>1200</Numberc>
</Numeric>
</AllTypes>
<AllTypes>
<Numeric>
<Byteintc>2</Byteintc>
<Smallintc>400</Smallintc>
<Intc>400</Intc>
<Floatc>400</Floatc>
<Decimalc>400</Decimalc>
<Numberc>1400</Numberc>
</Numeric>
</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">
<Transaction>
<Update>
<Table>
<xsltuser.DefaultValue3 pkey="C_Byteintc">
<xsl:for-each select="AllTypes">
<Row>
<C_Id genexp="cast(? as int)" default="10">
<xsl:value-of select="Numeric/Bytec"/>
</C_Id>
<C_Byteintc genexp="cast(? as byteint)">
<xsl:value-of select="Numeric/Byteintc"/>
</C_Byteintc>
<C_Smallintc genexp="cast(? as smallint)" >
<xsl:value-of select="Numeric/Smallintc"/>
</C_Smallintc>
<C_Intc genexp="cast(? as integer)">
<xsl:value-of select="Numeric/Intc"/>
</C_Intc>
</Row>
</xsl:for-each>
</xsltuser.DefaultValue3>
</Table>
</Update>
</Transaction>
</xsl:template>
</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