xml shredding via XSLT_SHRED_BATCH

Database
Enthusiast

xml shredding via XSLT_SHRED_BATCH

I am having an issue shredding some xml. In the below I provide ddl for target table as well as table to hold the xml document. Wehn I run the stored procedure TD_SYSXML.XSLT_SHRED_BATCH it returns a zero code but when I query the target table CUSTDTL the table is empty so no data has been inserted. Since there is no visible error I am not sure what else to look at. I am running TD 16 on VMWare. Thanks for any suggestions.

 

Final Target Table DDL

CREATE SET TABLE CUSTDTL ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT (
------------------ID CHAR(9),
NAME VARCHAR(256),
ADDRESS VARCHAR(256),
PHONE1 VARCHAR(16),
PHONE2 VARCHAR(16),
FAX VARCHAR(16),
EMAIL VARCHAR(64)
) PRIMARY INDEX CUSTID_NUPI (NAME);

 

 DDL To Hold XML Doc

CREATE TABLE customer (
customerID INTEGER,
customerName VARCHAR(256),
customerXML XML )
PRIMARY INDEX (customerID);

 

Insert Xml Doc To Customer table

INSERT INTO customer (1, 'John Hancock', CREATEXML('<?xml
version="1.0" encoding="UTF-8"?>
<Customer ID="C00-10101">
<Name>John Hancock</Name>
<Address>100 1st Street, San Francisco, CA 94118</Address>
<Phone1>(858)555-1234</Phone1>
<Phone2>(858)555-9876</Phone2>
<Fax>(858)555-9999</Fax>
<Email>John@somecompany.com</Email>
<Order Number="NW-01-16366" Date="2012-02-28">
<Contact>Mary Jane</Contact>
<Phone>(987)654-3210</Phone>
<ShipTo>Some company, 2467 Pioneer Road, San Francisco, CA 94117</ShipTo>
<SubTotal>434.99</SubTotal>
<Tax>32.55</Tax>
<Total>467.54</Total>
<Item ID="001">
<Quantity>10</Quantity>
<PartNumber>F54709</PartNumber>
<Description>Motorola S10-HD Bluetooth Stereo Headphones</Description>
<UnitPrice>29.50</UnitPrice>
<Price>295.00</Price>
</Item>
<Item ID="101">
<Quantity>1</Quantity>
<PartNumber>Z19743</PartNumber>
<Description>Motorola Milestone XT800 Cell Phone</Description>
<UnitPrice>139.99</UnitPrice>
<Price>139.99</Price>
</Item>
</Order>
</Customer>'));

 

Use Stored Procedure To Shred XML and Insert to Target

CALL TD_SYSXML.XSLT_SHRED_BATCH('SELECT CUSTOMERID, CUSTOMERXML FROM JQUIRK.CUSTOMER',
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>
<JQUIRK.CUSTDTL>
<xsl:for-each select="Customer">
<Row>
<NAME genexp="cast(? as varchar(256))"><xsl:value-of select="Name"/></NAME>
<ADDRESS genexp="cast(? as varchar(256))"><xsl:value-of select="Address"/></ADDRESS>
<PHONE1 genexp="cast(? as varchar(16))"><xsl:value-of select="Phone1"/></PHONE1>
<PHONE2 genexp="cast(? as varchar(16))"><xsl:value-of select="Phone2"/></PHONE2>
<FAX genexp="cast(? as varchar(16))"><xsl:value-of select="Fax"/></FAX>
<EMAIL genexp="cast(? as varchar(64))"><xsl:value-of select="Email"/></EMAIL>
</Row>
</xsl:for-each>
</JQUIRK.CUSTDTL>
</Table>
</Insert>
</Transaction>
</xsl:template>
</xsl:stylesheet>'), NULL,:res);

1 REPLY
Teradata Employee

Re: xml shredding via XSLT_SHRED_BATCH

Hello jquirk,

 

the problem is, that your root element doesn't match.


jquirk wrote:

<xsl:template match="/Root">


There is no <Root> element in the XML.

So just change it to

<xsl:template match="/">

 

Regards,

Torsten