CALL Failed. 9134: AS_SHRED_BATCH: Error in function AS_SHRED: XML parsing error

Extensibility
Teradata Employee

CALL Failed. 9134: AS_SHRED_BATCH: Error in function AS_SHRED: XML parsing error

TD 15.00.6.13

I am performing below steps:
1. Creating annotated schema of an XML but the XML has additional elements (only elements not attributes) which are NOT defined in the schema and calling XML shredding procedure. It is completing successfully.
2. Creating annotated schema of an XML but the XML has additional elements along with attributes which are not defined in the schema and calling XML shredding procedure. It is failing.

 

 

CASES:

SyntaxEditor Code Snippet

/*Below example has additional element "<aa>" in the XML but not defined in the Annotated Schema. The XML Shredding is working fine and loading data into table*/

---------------- Load XML into Table -----------------CREATE MULTISET TABLE TFP0002_FLT_PLAN_XMLCONTENT ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO     (
      ID BIGINT GENERATED BY DEFAULT AS IDENTITY           (START WITH 1 
            INCREMENT BY 1 
            MINVALUE 1 
            MAXVALUE 9999999 
            NO CYCLE),
      XML_CONTENT XML)PRIMARY INDEX ( ID );

INSERT INTO TFP0002_FLT_PLAN_XMLCONTENT VALUES (1, CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" ID="8567" computedTime="2016-05-24T09:00:47Z" >
    <AirportDataList>
        <AirportData>
            <Airport airportFunction="DepartureAirport">
                <AirportICAOCode>DICAO</AirportICAOCode>
                <AirportIATACode>DIATA</AirportIATACode>
            </Airport>
        </AirportData>
        <AirportData>
            <Airport airportFunction="ArrivalAirport">
                <AirportICAOCode>AICAO</AirportICAOCode>
                <AirportIATACode>AIATA</AirportIATACode>
            </Airport>
            <SuitablePeriod from="2016-01-01T01:01:00Z" until="2016-01-02T01:01:00Z"/>
            <aa>aa</aa>
        </AirportData>
    </AirportDataList>
</Data>
'));



--------------------- Load Annotated XSD into Table -----------------CREATE MULTISET TABLE TFP0001_FLT_PLAN_XSDSCHEMA ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO     (
      SCHEMA_ID BIGINT GENERATED BY DEFAULT AS IDENTITY           (START WITH 1 
            INCREMENT BY 1 
            MINVALUE 1 
            MAXVALUE 9999999 
            NO CYCLE),
      ANNOTATED_SCHEMA XML)PRIMARY INDEX ( SCHEMA_ID );

INSERT INTO TFP0001_FLT_PLAN_XSDSCHEMA VALUES (1,CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:td="http://www.teradata.com/xml">
    <xs:annotation>
        <xs:appinfo>
            <context xmlFeatureVersion="1.0" xmlns="http://www.teradata.com/xml">
                <defaultDatabase>DTEMP</defaultDatabase>
                <defaultEncoding>ISO-8859-1</defaultEncoding>
                <rootElement ref="FP"/>
                <transaction>
                    <operation type="insert">
                        <table name="TEST123">
                            <column name="FLT_PLAN_ID" ref="fltPlanId" path="Data/@ID">
                                <sqltype name="varchar"/>
                            </column>
                            <column name="COMPUTED_TIME" ref="computedTime" path="Data/@computedTime">
                                <sqltype name="varchar"/>
                            </column>
                            <column name="AIP_IATA_CODE" ref="ADL_2_AirportIATACode" path="Data/AirportDataList/AirportData/Airport/AirportIATACode">
                                <sqltype name="varchar"/>
                            </column>
                            <column name="AIP_ICAO_CODE" ref="ADL_1_AirportICAOCode" path="Data/AirportDataList/AirportData/Airport/AirportICAOCode">
                                <sqltype name="varchar"/>
                            </column>
                            <column name="AIP_USAGE_TYPE_CODE" ref="ADL_3_airportFunction" path="Data/AirportDataList/AirportData/Airport/@airportFunction">
                                <sqltype name="varchar"/>
                            </column>
                            <column name="EARLIEST_ARVL_UTC_TSMP" ref="ADL_10_from" path="Data/AirportDataList/AirportData/SuitablePeriod/@from">
                                <sqltype name="varchar"/>
                            </column>
                            <column name="LATEST_ARVL_UTC_TSMP" ref="ADL_11_until" path="Data/AirportDataList/AirportData/SuitablePeriod/@until">
                                <sqltype name="varchar"/>
                            </column>
                        </table>
                    </operation>
                </transaction>
            </context>
        </xs:appinfo>
    </xs:annotation>
    <xs:element name="Data" td:item="FP">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="AirportDataList" td:item="ADL">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="AirportData" maxOccurs="unbounded" minOccurs="0">
                                <xs:complexType>
                                    <xs:sequence>
                                        <xs:element name="Airport">
                                            <xs:complexType>
                                                <xs:sequence>
                                                    <xs:element type="xs:string" name="AirportICAOCode" td:item="ADL_1_AirportICAOCode"/>
                                                    <xs:element type="xs:string" name="AirportIATACode" td:item="ADL_2_AirportIATACode"/>
                                                </xs:sequence>
                                                <xs:attribute type="xs:string" name="airportFunction" use="optional" td:item="ADL_3_airportFunction"/>
                                            </xs:complexType>
                                        </xs:element>
                                        <xs:element name="SuitablePeriod">
                                            <xs:complexType>
                                                <xs:simpleContent>
                                                    <xs:extension base="xs:string">
                                                        <xs:attribute type="xs:dateTime" name="from" use="optional" td:item="ADL_10_from"/>
                                                        <xs:attribute type="xs:dateTime" name="until" use="optional" td:item="ADL_11_until"/>
                                                    </xs:extension>
                                                </xs:simpleContent>
                                            </xs:complexType>
                                        </xs:element>
                                    </xs:sequence>
                                </xs:complexType>
                            </xs:element>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
            <xs:attribute type="xs:string" name="ID" td:item="fltPlanId"/>
            <xs:attribute type="xs:dateTime" name="computedTime" td:item="computedTime"/>
        </xs:complexType>
    </xs:element>
</xs:schema>'));






----------------------------- Target Table ---------------------------------CREATE MULTISET TABLE DTEMP.TEST123 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO     (
      FLT_PLAN_ID VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      COMPUTED_TIME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      AIP_IATA_CODE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      AIP_ICAO_CODE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      AIP_USAGE_TYPE_CODE VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      EARLIEST_ARVL_UTC_TSMP VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      LATEST_ARVL_UTC_TSMP VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC      )PRIMARY INDEX ( FLT_PLAN_ID );







----------------------------- XML Shredding Procedure -------------------------REPLACE PROCEDURE MY_SHREDBATCH_SP(IN sourceDataQuery varchar(6000),IN annotatedSchemaID BIGINT,OUT resultCode varchar(128)
)SPMAIN:BEGIN
DECLARE annotatedSchema XML;
DECLARE inputCursor CURSOR FOR SELECT ANNOTATED_SCHEMA from TFP0001_FLT_PLAN_XSDSCHEMA WHERE SCHEMA_ID = :annotatedSchemaID;
OPEN inputCursor;
FETCH inputCursor INTO annotatedSchema;
IF (SQLSTATE <> '02000') THEN
BEGIN
CALL TD_SYSXML.AS_SHRED_BATCH(:sourceDataQuery, :annotatedSchema,NULL, :resultCode);
END;
ELSE
SET resultCode = -1;
END IF;
CLOSE inputCursor;
END SPMAIN;


--------------------------- Call XML Shredding stored procedure ---------------------DELETE FROM DTEMP.TEST123;
CALL MY_SHREDBATCH_SP('SELECT ID, XML_CONTENT FROM TFP0002_FLT_PLAN_XMLCONTENT',1,RES);
SELECT * FROM DTEMP.TEST123;




tEST CASE 2:

SyntaxEditor Code Snippet
/*Below example has additional element "<aa>" with attribute "attr" in the XML but not defined in the Annotated Schema.
The XML Shredding procedure is failing*/

---------------- Load XML into Table -----------------CREATE MULTISET TABLE TFP0002_FLT_PLAN_XMLCONTENT ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO     (
      ID BIGINT GENERATED BY DEFAULT AS IDENTITY           (START WITH 1 
            INCREMENT BY 1 
            MINVALUE 1 
            MAXVALUE 9999999 
            NO CYCLE),
      XML_CONTENT XML)PRIMARY INDEX ( ID );

INSERT INTO TFP0002_FLT_PLAN_XMLCONTENT VALUES (1, CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<Data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" ID="8567" computedTime="2016-05-24T09:00:47Z" >
    <AirportDataList>
        <AirportData>
            <Airport airportFunction="DepartureAirport">
                <AirportICAOCode>DICAO</AirportICAOCode>
                <AirportIATACode>DIATA</AirportIATACode>
            </Airport>
        </AirportData>
        <AirportData>
            <Airport airportFunction="ArrivalAirport">
                <AirportICAOCode>AICAO</AirportICAOCode>
                <AirportIATACode>AIATA</AirportIATACode>
            </Airport>
            <SuitablePeriod from="2016-01-01T01:01:00Z" until="2016-01-02T01:01:00Z"/>
            <aa attr="1234">aa</aa>
        </AirportData>
    </AirportDataList>
</Data>
'));



--------------------- Load Annotated XSD into Table -----------------CREATE MULTISET TABLE TFP0001_FLT_PLAN_XSDSCHEMA ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO     (
      SCHEMA_ID BIGINT GENERATED BY DEFAULT AS IDENTITY           (START WITH 1 
            INCREMENT BY 1 
            MINVALUE 1 
            MAXVALUE 9999999 
            NO CYCLE),
      ANNOTATED_SCHEMA XML)PRIMARY INDEX ( SCHEMA_ID );

INSERT INTO TFP0001_FLT_PLAN_XSDSCHEMA VALUES (1,CREATEXML('<?xml version="1.0" encoding="UTF-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:td="http://www.teradata.com/xml">
    <xs:annotation>
        <xs:appinfo>
            <context xmlFeatureVersion="1.0" xmlns="http://www.teradata.com/xml">
                <defaultDatabase>DTEMP</defaultDatabase>
                <defaultEncoding>ISO-8859-1</defaultEncoding>
                <rootElement ref="FP"/>
                <transaction>
                    <operation type="insert">
                        <table name="TEST123">
                            <column name="FLT_PLAN_ID" ref="fltPlanId" path="Data/@ID">
                                <sqltype name="varchar"/>
                            </column>
                            <column name="COMPUTED_TIME" ref="computedTime" path="Data/@computedTime">
                                <sqltype name="varchar"/>
                            </column>
                            <column name="AIP_IATA_CODE" ref="ADL_2_AirportIATACode" path="Data/AirportDataList/AirportData/Airport/AirportIATACode">
                                <sqltype name="varchar"/>
                            </column>
                            <column name="AIP_ICAO_CODE" ref="ADL_1_AirportICAOCode" path="Data/AirportDataList/AirportData/Airport/AirportICAOCode">
                                <sqltype name="varchar"/>
                            </column>
                            <column name="AIP_USAGE_TYPE_CODE" ref="ADL_3_airportFunction" path="Data/AirportDataList/AirportData/Airport/@airportFunction">
                                <sqltype name="varchar"/>
                            </column>
                            <column name="EARLIEST_ARVL_UTC_TSMP" ref="ADL_10_from" path="Data/AirportDataList/AirportData/SuitablePeriod/@from">
                                <sqltype name="varchar"/>
                            </column>
                            <column name="LATEST_ARVL_UTC_TSMP" ref="ADL_11_until" path="Data/AirportDataList/AirportData/SuitablePeriod/@until">
                                <sqltype name="varchar"/>
                            </column>
                        </table>
                    </operation>
                </transaction>
            </context>
        </xs:appinfo>
    </xs:annotation>
    <xs:element name="Data" td:item="FP">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="AirportDataList" td:item="ADL">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="AirportData" maxOccurs="unbounded" minOccurs="0">
                                <xs:complexType>
                                    <xs:sequence>
                                        <xs:element name="Airport">
                                            <xs:complexType>
                                                <xs:sequence>
                                                    <xs:element type="xs:string" name="AirportICAOCode" td:item="ADL_1_AirportICAOCode"/>
                                                    <xs:element type="xs:string" name="AirportIATACode" td:item="ADL_2_AirportIATACode"/>
                                                </xs:sequence>
                                                <xs:attribute type="xs:string" name="airportFunction" use="optional" td:item="ADL_3_airportFunction"/>
                                            </xs:complexType>
                                        </xs:element>
                                        <xs:element name="SuitablePeriod">
                                            <xs:complexType>
                                                <xs:simpleContent>
                                                    <xs:extension base="xs:string">
                                                        <xs:attribute type="xs:dateTime" name="from" use="optional" td:item="ADL_10_from"/>
                                                        <xs:attribute type="xs:dateTime" name="until" use="optional" td:item="ADL_11_until"/>
                                                    </xs:extension>
                                                </xs:simpleContent>
                                            </xs:complexType>
                                        </xs:element>
                                    </xs:sequence>
                                </xs:complexType>
                            </xs:element>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
            <xs:attribute type="xs:string" name="ID" td:item="fltPlanId"/>
            <xs:attribute type="xs:dateTime" name="computedTime" td:item="computedTime"/>
        </xs:complexType>
    </xs:element>
</xs:schema>'));






----------------------------- Target Table ---------------------------------CREATE MULTISET TABLE DTEMP.TEST123 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO     (
      FLT_PLAN_ID VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      COMPUTED_TIME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      AIP_IATA_CODE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      AIP_ICAO_CODE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      AIP_USAGE_TYPE_CODE VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      EARLIEST_ARVL_UTC_TSMP VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      LATEST_ARVL_UTC_TSMP VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC      )PRIMARY INDEX ( FLT_PLAN_ID );







----------------------------- XML Shredding Procedure -------------------------REPLACE PROCEDURE MY_SHREDBATCH_SP(IN sourceDataQuery varchar(6000),IN annotatedSchemaID BIGINT,OUT resultCode varchar(128)
)SPMAIN:BEGIN
DECLARE annotatedSchema XML;
DECLARE inputCursor CURSOR FOR SELECT ANNOTATED_SCHEMA from TFP0001_FLT_PLAN_XSDSCHEMA WHERE SCHEMA_ID = :annotatedSchemaID;
OPEN inputCursor;
FETCH inputCursor INTO annotatedSchema;
IF (SQLSTATE <> '02000') THEN
BEGIN
CALL TD_SYSXML.AS_SHRED_BATCH(:sourceDataQuery, :annotatedSchema,NULL, :resultCode);
END;
ELSE
SET resultCode = -1;
END IF;
CLOSE inputCursor;
END SPMAIN;


--------------------------- Call XML Shredding stored procedure ---------------------DELETE FROM DTEMP.TEST123;
CALL MY_SHREDBATCH_SP('SELECT ID, XML_CONTENT FROM TFP0002_FLT_PLAN_XMLCONTENT',1,RES);
SELECT * FROM DTEMP.TEST123;



Could anyone please help me on this.

Thanks ,
Sunder