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