Use XSLT_SHRED_BATCH in multiple operations: Insert, Update, Delete, and Upsert .
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 tables for use in the XSLT_SHRED_BATCH multiple operations example.
DELETE Customer;
DELETE Orders;
DELETE Dealer;
DELETE Input_Docs;
INSERT INTO Orders(o_orderkey) values (12610);
INSERT INTO Orders(o_orderkey) values (12611);
INSERT INTO Dealer(d_id) values (12610);
INSERT INTO Input_Docs VALUES(1, CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<root>
<customers>
<customer>
<c_custkey>1805</c_custkey>
<c_name>Customer#000001805</c_name>
<c_address>ZERs4Cu5lQTYD</c_address>
<c_nationkey>9</c_nationkey>
<c_phone>19-679-706-1096</c_phone>
<c_acctbal>-274.75</c_acctbal>
<c_mktsegment>AUTOMOBILE</c_mktsegment>
<c_comment>quickly unusual courts</c_comment>
</customer>
<customer>
<c_custkey>1806</c_custkey>
<c_name>Customer#000001806</c_name>
<c_address>BB6Vr7W rSIpWKp</c_address>
<c_nationkey>9</c_nationkey>
<c_phone>19-872-322-3433</c_phone>
<c_acctbal>254.17</c_acctbal>
<c_mktsegment>MACHINERY</c_mktsegment>
<c_comment>ideas are blithely</c_comment>
</customer>
</customers>
<orders>
<order>
<o_orderkey>12610</o_orderkey>
<o_custkey>4228</o_custkey>
<o_orderstatus>F</o_orderstatus>
<o_totalprice>9096.92</o_totalprice>
<o_orderdate>1900-01-17T00:00:00.0Z</o_orderdate>
<o_orderpriority>5/LOW</o_orderpriority>
<o_clerk>Clerk#000000952</o_clerk>
<o_shippriority>0</o_shippriority>
<o_comment>ironic requests are furiously</o_comment>
</order>
<order>
<o_orderkey>12611</o_orderkey>
<o_custkey>2050</o_custkey>
<o_orderstatus>O</o_orderstatus>
<o_totalprice>224970.76</o_totalprice>
<o_orderdate>1900-01-13T00:00:00.0Z</o_orderdate>
<o_orderpriority>3/MEDIUM</o_orderpriority>
<o_clerk>Clerk#000000579</o_clerk>
<o_shippriority>0</o_shippriority>
<o_comment>fluffily ironic asympto</o_comment>
</order>
<order>
<o_orderkey>12612</o_orderkey>
<o_custkey>72163</o_custkey>
<o_orderstatus>F</o_orderstatus>
<o_totalprice>197724.39</o_totalprice>
<o_orderdate>1900-01-08T00:00:00.0Z</o_orderdate>
<o_orderpriority>5/LOW</o_orderpriority>
<o_clerk>Clerk#000000381</o_clerk>
<o_shippriority>0</o_shippriority>
<o_comment>furious, regular deposits</o_comment>
</order>
</orders>
<dealers>
<dealer>
<d_id>12610</d_id>
<d_name>VBIT</d_name>
<d_address>HYD</d_address>
<d_comment>AP Based dealer</d_comment>
</dealer>
<dealer>
<d_id>12611</d_id>
<d_name>VBIT</d_name>
<d_address>HYD</d_address>
<d_comment>AP Based dealer</d_comment>
</dealer>
</dealers>
</root>'));
Run XSLT_SHRED_BATCH with Insert, Update, Delete, and Upsert operations.
CALL TD_SYSXML.XSLT_SHRED_BATCH('SEL id, xmldoc FROM xsltuser.Input_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="/root">
<Transaction>
<Insert>
<Table>
<xsltuser.customer pkey="c_custkey">
<xsl:for-each select="customers/customer">
<Row>
<c_custkey>
<xsl:value-of select="c_custkey"/>
</c_custkey>
<c_name>
<xsl:value-of select="c_name"/>
</c_name>
<c_address>
<xsl:value-of select="c_address"/>
</c_address>
<c_nationkey>
<xsl:value-of select="c_nationkey"/>
</c_nationkey>
<c_phone>
<xsl:value-of select="c_phone"/>
</c_phone>
<c_acctbal>
<xsl:value-of select="c_acctbal"/>
</c_acctbal>
<c_mktsegment>
<xsl:value-of select="c_mktsegment"/>
</c_mktsegment>
<c_comment>
<xsl:value-of select="c_comment"/>
</c_comment>
</Row>
</xsl:for-each>
</xsltuser.customer>
</Table>
</Insert>
<Update>
<Table>
<xsltuser.orders pkey="o_orderkey">
<xsl:for-each select="orders/order">
<Row>
<o_orderkey>
<xsl:value-of select="o_orderkey"/>
</o_orderkey>
<o_custkey>
<xsl:value-of select="o_custkey"/>
</o_custkey>
<o_orderstatus>
<xsl:value-of select="o_orderstatus"/>
</o_orderstatus>
<o_totalprice>
<xsl:value-of select="o_totalprice"/>
</o_totalprice>
<o_orderdate genexp="cast(? as date)">
<xsl:value-of select="substring(o_orderdate,1,10)"/>
</o_orderdate>
<o_orderpriority>
<xsl:value-of select="o_orderpriority"/>
</o_orderpriority>
<o_clerk>
<xsl:value-of select="o_clerk"/>
</o_clerk>
<o_shippriority>
<xsl:value-of select="o_shippriority"/>
</o_shippriority>
<o_comment>
<xsl:value-of select="o_comment"/>
</o_comment>
</Row>
</xsl:for-each>
</xsltuser.orders>
</Table>
</Update>
<Delete>
<Table>
<xsltuser.dealer pkey="d_id">
<xsl:for-each select="dealers/dealer">
<Row>
<d_id><xsl:value-of select="d_id"/></d_id>
<d_name>
<xsl:value-of select="d_name"/>
</d_name>
</Row>
</xsl:for-each>
</xsltuser.dealer>
</Table>
</Delete>
<Upsert>
<Table>
<xsltuser.dealer pkey="d_id">
<xsl:for-each select="dealers/dealer">
<Row>
<d_id><xsl:value-of select="d_id"/></d_id>
<d_name>
<xsl:value-of select="d_name"/>
</d_name>
<d_address>
<xsl:value-of select="d_address"/>
</d_address>
<d_comment>
<xsl:value-of select="d_comment"/>
</d_comment>
</Row>
</xsl:for-each>
</xsltuser.dealer>
</Table>
</Upsert>
</Transaction>
</xsl:template>
</xsl:stylesheet>'), NULL,:res);
Result: To view the updated data in the target table, run:
SEL c_custkey, c_name, c_phone, c_acctbal, c_mktsegment FROM Customer;
c_custkey c_name c_phone c_acctbal c_mktsegment
--------- ------------------- --------------- --------- -------------
1805 Customer#000001805 19-679-706-1096 -274.75 AUTOMOBILE
1806 Customer#000001806 19-872-322-3433 254.17 MACHINERY
Result: To view the updated data in the target table, run:
SEL o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_clerk FROM Orders;
o_orderkey o_custkey o_orderstatus o_totalprice o_clerk
----------- --------- ------------- ------------ ---------------
12611 2050 O 224970.76 Clerk#000000579
12610 4228 F 9096.92 Clerk#000000952
Result: To view the updated data in the target table, run: SEL * FROM Dealer;
d_id d_name d_address d_comment ------ ---------- ----------- ----------------- 12611 VBIT HYD AP Based dealer 12610 VBIT HYD AP Based dealer