Example: XSLT_SHRED_BATCH <xsl:copy-of select=""> - Advanced SQL Engine - Teradata Database

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.

DELETE Offer;
DELETE Input_Docs;

INSERT INTO input_docs VALUES(1,
 CREATEXML('<?xml version="1.0"?>
	<Root>
	  <predictixOfferMessage>
		  <Offer><offerid>1000001</offerid></Offer>
		  <mediaBlock>
			  <mediaBlockid>90000000010001</mediaBlockid>
			  <parentofferid>1001</parentofferid>
		  </mediaBlock>
	  </predictixOfferMessage>
	  <predictixOfferMessage>
		  <Offer><offerid>1000002</offerid></Offer>
		  <mediaBlock>
			  <mediaBlockid>90000000010002</mediaBlockid>
			  <parentofferid>1002</parentofferid>
		  </mediaBlock>
	  </predictixOfferMessage>
	</Root>'));

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 id, xmldoc FROM xsltuser.Input_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 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.Offer>
     <xsl:for-each select="predictixOfferMessage">
      <Row>
       <xsl:copy-of select="Offer/offerid"/>
       <xsl:copy-of select="mediaBlock/mediaBlockid"/>
      </Row>
     </xsl:for-each>
    </xsltuser.Offer>
   </Table>
  </Insert>
 </Transaction>
</xsl:template>
</xsl:stylesheet>'), NULL,:res);

offerid     mediaBlockid
----------  ---------------------------------
1000001     90000000010001
1000002     90000000010002