Example: XSLT_SHRED_BATCH Multiple Operations - Advanced SQL Engine - Teradata Database

XML Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
amr1556233250442.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1140
lifecycle
previous
Product Category
Teradata Vantageā„¢

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