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