Help in XML shredding

Database

Help in XML shredding

Hi, apologies if this is the wrong forum.

I am new to XML shredding and wondered if anybody could advise why the call on the procedure at the end of the following fails saying "CALL Failed.  [9134] AS_SHRED_BATCH:Error in function ASSHRED_GETTABLES: Invalid annotated schema. "

Thanks for any advice.


--  Create a file with the XML document

CREATE MULTISET TABLE DEV_SANDPIT.SB_XML
(
Identifier CHAR(14)
,
File_xml XML
)
PRIMARY INDEX (Identifier);

INSERT INTO DEV_SANDPIT.SB_XML
(
'COLL_STOP_STG'
,
NEW XML('<?xml version="1.0" ?>
<!DOCTYPE FILE [
<!ELEMENT FILE (record)*>
<!ELEMENT record (TourStopId,OpuNo,EmployeeId,StopStatus,PickupStatus,PickupDtTm,GpsStrength,Latitude,Longitude,AmendStamp)>
<!ELEMENT TourStopId (#PCDATA)>
<!ELEMENT OpuNo (#PCDATA)>
<!ELEMENT EmployeeId (#PCDATA)>
<!ELEMENT StopStatus (#PCDATA)>
<!ELEMENT PickupStatus (#PCDATA)>
<!ELEMENT PickupDtTm (#PCDATA)>
<!ELEMENT GpsStrength (#PCDATA)>
<!ELEMENT Latitude (#PCDATA)>
<!ELEMENT Longitude (#PCDATA)>
<!ELEMENT AmendStamp (#PCDATA)>
]><FILE><record>
<TourStopId>699353422</TourStopId>
<OpuNo>7273</OpuNo>
<EmployeeId>S16478</EmployeeId>
<StopStatus>COMPOK</StopStatus>
<PickupStatus>CA</PickupStatus>
<PickupDtTm>2015-01-09T16:03:00</PickupDtTm>
<GpsStrength></GpsStrength>
<Latitude></Latitude>
<Longitude></Longitude>
<AmendStamp>2015-01-09T16:04:29</AmendStamp>
</record></FILE>'
));

-- Create Target table

CREATE TABLE DEV_SANDPIT.COLL_STOP_WORK
(
TourStopId VARCHAR(100)
)
;

-- Create Schema repository

CREATE TABLE DEV_SANDPIT.SB_SHREDMAPPING
(
SCHEMA_ID VARCHAR(32)
,
ANNOTATED_SCHEMA XML
)
PRIMARY INDEX (SCHEMA_ID);

INSERT INTO DEV_SANDPIT.SB_SHREDMAPPING
(
'COLL_STOP_WORK',
NEW XML('<?xml version="1.0" encoding="UTF-8"?>
<xs:schema 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>DEV_SANDPIT</defaultDatabase>
<defaultEncoding>ISO-8859-1</defaultEncoding>
<rootElement ref="_coll_stop_"/>
<TRANSACTION>
<operation TYPE="insert">
<TABLE NAME="COLL_STOP_WORK">
<COLUMN NAME="TourStopId" ref="_TourStopId_" PATH="file/record/TourStopId">
<sqltype NAME="varchar"/>
</COLUMN>
</TABLE>
</operation>
</TRANSACTION>
</context>
</xs:appinfo>
</xs:annotation>

<xs:element NAME="file" td:item="_coll_stop_">
<xs:complexType>
<xs:sequence>
<xs:element NAME="record">
<xs:complexType>
<xs:sequence>
<xs:element NAME="TourStopId" TYPE="xs:string" td:item="_TourStopId_"></xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>

</xs:schema>
'
)
)
;

REPLACE PROCEDURE DEV_SANDPIT.SB_SHREDBATCH_SP
(
IN sourceDataQuery VARCHAR(6000),
IN annotatedSchemaID VARCHAR(32),
OUT resultCode VARCHAR(128)
)
SPMAIN:BEGIN
DECLARE annotatedSchema XML;
DECLARE inputCursor CURSOR FOR SELECT ANNOTATED_SCHEMA FROM DEV_SANDPIT.SB_SHREDMAPPING 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 DEV_SANDPIT.SB_SHREDBATCH_SP('SEL * FROM DEV_SANDPIT.SB_XML;','COLL_STOP_WORK',rc);

3 REPLIES

Re: Help in XML shredding

I may have resolved this - it seems the keywords in the Annotation part of the schema (e.g.TABLE, COLUMN, NAME, TRANSACTION etc in the example above) need to be in lower case in order to be interpreted by the xml SPs and functions.

Re: Help in XML shredding

I am now faced with a different problem. I am now encountering the error "CALL Failed. 9134:  AS_SHRED_BATCH:Error in function AS_SHREDTB: Internal error.  " on the execution of the call. I am however able to get this to run by removing the DTD statement from the xml - the part that reads:

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 934 StartFragment: 314 EndFragment: 902 StartSelection: 314 EndSelection: 314


<!DOCTYPE file  [
<!ELEMENT file (record)*>
<!ELEMENT record (TourStopId,OpuNo,EmployeeId,StopStatus,PickupStatus,PickupDtTm,GpsStrength,Latitude,Longitude,AmendStamp)>
<!ELEMENT TourStopId (#PCDATA)>
<!ELEMENT OpuNo (#PCDATA)>
<!ELEMENT EmployeeId (#PCDATA)>
<!ELEMENT StopStatus (#PCDATA)>
<!ELEMENT PickupStatus (#PCDATA)>
<!ELEMENT PickupDtTm (#PCDATA)>
<!ELEMENT GpsStrength (#PCDATA)>
<!ELEMENT Latitude (#PCDATA)>
<!ELEMENT Longitude (#PCDATA)>
<!ELEMENT AmendStamp (#PCDATA)>
]>

Has anybody else encountered issues using the AS_SHRED_BATCH function on XML containing Doctype Ddeclarations?

The full code that fails is:

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 19068 StartFragment: 314 EndFragment: 19036 StartSelection: 314 EndSelection: 314


-- Create a file with the XML document

DROP TABLE DEV_SANDPIT.SB_XML
;

CREATE MULTISET TABLE DEV_SANDPIT.SB_XML
(
Identifier INTEGER
,
File_xml XML
)
PRIMARY INDEX (Identifier);

INSERT INTO DEV_SANDPIT.SB_XML
(
1
,
NEW XML('<?xml version="1.0" ?>
<!DOCTYPE file [
<!ELEMENT file (record)*>
<!ELEMENT record (TourStopId,OpuNo,EmployeeId,StopStatus,PickupStatus,PickupDtTm,GpsStrength,Latitude,Longitude,AmendStamp)>
<!ELEMENT TourStopId (#PCDATA)>
<!ELEMENT OpuNo (#PCDATA)>
<!ELEMENT EmployeeId (#PCDATA)>
<!ELEMENT StopStatus (#PCDATA)>
<!ELEMENT PickupStatus (#PCDATA)>
<!ELEMENT PickupDtTm (#PCDATA)>
<!ELEMENT GpsStrength (#PCDATA)>
<!ELEMENT Latitude (#PCDATA)>
<!ELEMENT Longitude (#PCDATA)>
<!ELEMENT AmendStamp (#PCDATA)>
]>
<file><record>
<TourStopId>708454870</TourStopId>
<OpuNo>7271</OpuNo>
<EmployeeId>063280</EmployeeId>
<StopStatus>COMPOK</StopStatus>
<PickupStatus>CA</PickupStatus>
<PickupDtTm>2015-02-02T12:08:00</PickupDtTm>
<GpsStrength>2.600</GpsStrength>
<Latitude>51.879810</Latitude>
<Longitude>0.550454</Longitude>
<AmendStamp>2015-02-02T12:10:11</AmendStamp>
</record></file>'
));

-- Create Target table

DROP TABLE DEV_SANDPIT.COLL_STOP_WORK;

CREATE TABLE DEV_SANDPIT.COLL_STOP_WORK
(
TourStopId VARCHAR(100)
)
;

-- Create Schema repository

DROP TABLE DEV_SANDPIT.SB_SHREDMAPPING;

CREATE TABLE DEV_SANDPIT.SB_SHREDMAPPING
(
SCHEMA_ID VARCHAR(32)
,
ANNOTATED_SCHEMA XML
)
PRIMARY INDEX (SCHEMA_ID);

INSERT INTO DEV_SANDPIT.SB_SHREDMAPPING
(
'COLL_STOP_WORK',
NEW XML('<?xml version="1.0" encoding="UTF-8"?>
<xs:schema 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>DEV_SANDPIT</defaultDatabase>
<defaultEncoding>ISO-8859-1</defaultEncoding>
<rootElement ref="_file_item_"/>
<transaction>
<operation type="insert">
<table name="COLL_STOP_WORK">
<column name="TourStopId" ref="_TourStopId_item_" path="file/record/TourStopId">
<sqltype name="varchar"/>
</column>
</table>
</operation>
</transaction>
</context>
</xs:appinfo>
</xs:annotation>

<xs:element name="file" td:item="_file_item_">
<xs:complexType>
<xs:sequence>
<xs:element name="record">
<xs:complexType>
<xs:sequence>
<xs:element type="xs:string" name="TourStopId" td:item="_TourStopId_item_"></xs:element>
<xs:element type="xs:string" name="OpuNo"></xs:element>
<xs:element type="xs:string" name="EmployeeId"></xs:element>
<xs:element type="xs:string" name="StopStatus"></xs:element>
<xs:element type="xs:string" name="PickupStatus"></xs:element>
<xs:element type="xs:string" name="PickupDtTm"></xs:element>
<xs:element type="xs:string" name="GpsStrength"></xs:element>
<xs:element type="xs:string" name="Latitude"></xs:element>
<xs:element type="xs:string" name="Longitude"></xs:element>
<xs:element type="xs:string" name="AmendStamp"></xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>

</xs:schema>
'
)
)
;

GRANT
SELECT
ON DEV_SANDPIT.SB_XML TO TD_SYSXML;

GRANT
INSERT
ON DEV_SANDPIT.COLL_STOP_WORK TO TD_SYSXML;

REPLACE PROCEDURE DEV_SANDPIT.SB_SHREDBATCH_SP
(
IN sourceDataQuery VARCHAR(6000),
IN annotatedSchemaID VARCHAR(32),
OUT resultCode VARCHAR(128)
)
SPMAIN:
BEGIN
DECLARE annotatedSchema XML;
DECLARE inputCursor CURSOR FOR
SELECT ANNOTATED_SCHEMA
FROM DEV_SANDPIT.SB_SHREDMAPPING
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 DEV_SANDPIT.SB_SHREDBATCH_SP('SEL * FROM DEV_SANDPIT.SB_XML;','COLL_STOP_WORK',rc);

Teradata Employee

Re: Help in XML shredding

I am too facing the same error in function as shred gettables invalid annotated schema

below are my xml and xsd

<?xml version="1.0" encoding="UTF-8"?>

<!--XML row for DBQLLockXML-->


<LockContention QueryID="307191797674279970" CollectTimeStamp="2016-07-07T08:31:25.92" DelayStartTime="2016-07-07T08:26:27.0" AbortFlag="FALSE" TransactionCount="1">

<ContentionData  LockDelay="15442" LocalDeadLock="false" GlobalDeadLock="false" MultipleBlocker="true" vproc="0">

<BlockerRef>

<RequestRef>

<RequestID RequestID_1="0" RequestID_2="117"/>

<Step StepLevel="0" StepNum_1="1" StepNum_2="0" StatementNo="1"/>

<Transaction unique_1="24" unique_2="10172" vproc="30719" TransactionState="Active" TransactionStartTime="2016-07-07T04:26:27.0" />

</RequestRef>

<SessionRef  LogicalHostId="1" ZoneId="0">

<Session SessionID_1="0" SessionID_2="1812" SessionID_Combined="1812"/>

<User UserName="DBC" AccountName="DBC"/>

</SessionRef>

<LockData  LockTypeRequested="Read"  LockObjectRequested="T"  LockKind="RowHash Range" > </LockData>

<LockObject DatabaseName="STUDENTS" TableName="EMP_NAME"/>

<Job>

<Message MessageClass="9" MessageKind="15" MessageClassName="SYSMSGSTPCLASS" MessageKindName="INS"/>

<Operation WorkLevel="0" OperationType="Insert a row" JobType="AmpStep"/>

</Job>

</BlockerRef>

<BlockedRef>

<RequestRef> </RequestRef>

<SessionRef  LogicalHostId="1" ZoneId="0">

<Session SessionID_1="0" SessionID_2="1808" SessionID_Combined="1808"/>

</SessionRef>

<LockData  LockTypeRequested="Write"  LockObjectRequested="R"  LockKind="RowHash Range" StartRowHashInt="888902353"  > </LockData>

</BlockedRef>

</ContentionData>

<BlockedTrans  TransactionOrder="1">

<RequestRef>

<RequestID RequestID_1="0" RequestID_2="13"/>

<Step StepNum_1="3" StepNum_2="0"/>

<Transaction unique_1="24" unique_2="10152" vproc="30719" TransactionState="Inactive" TransactionStartTime="2016-07-07T04:25:03.29" />

</RequestRef>

<SessionRef  LogicalHostId="1" ZoneId="0">

<Session SessionID_1="0" SessionID_2="1812" SessionID_Combined="1812"/>

<User UserName="STUDENTS"/>

</SessionRef>

<LockData  LockStatus="Granted" LockTypeRequested="Read"  LockObjectRequested="T"  > </LockData>

<LockObject DatabaseName="STUDENTS" TableName="EMP_NAME"/>

</BlockedTrans>

</LockContention>

</DBQLLockXML>

XSD

<?xml version="1.0" encoding="UTF-8"?>

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"



  <xs:annotation>

    <xs:appinfo>

      <context xmlFeatureVersion="1.0" xmlns="http://www.teradata.com/xml">

        <defaultDatabase>STUDENTS</defaultDatabase>

        <defaultEncoding>ISO-8859-1</defaultEncoding>

        <rootElement ref="_DBQLLockXML_item_" />

        <transaction>

          <operation type="insert">

            <table name="dbql">

              <column name="AbortFlag" ref="_AbortFlag_item_" path="DBQLLockXML/LockContention/@AbortFlag">

                <sqltype name="varchar" />

              </column>

              <column name="QueryID" ref="_QueryID_item_" path="DBQLLockXML/LockContention/@QueryID">

                <sqltype name="varchar" />

              </column>

              <column name="CollectTimeStamp" ref="_CollectTimeStamp_item_" path="DBQLLockXML/LockContention/@CollectTimeStamp">

                <sqltype name="varchar" />

              </column>

              <column name="DelayStartTime" ref="_DelayStartTime_item_" path="DBQLLockXML/LockContention/@DelayStartTime">

                <sqltype name="varchar" />

              </column>

              <column name="TransactionCount" ref="_TransactionCount_item" path="DBQLLockXML/LockContention/@TransactionCount">

                <sqltype name="varchar" />

              </column>

            </table>

          </operation>

        </transaction>

      </context>

    </xs:appinfo>

  </xs:annotation>

  <xs:element name="DBQLLockXML" d:item="_DBQLLockXML_item_">

    <xs:complexType>

      <xs:sequence>

        <xs:element ref="d:LockContention" />

      </xs:sequence>

    </xs:complexType>

  </xs:element>

  <xs:element name="LockContention" d:item="_LockContention_item_">

    <xs:complexType>

      <xs:sequence>

        <xs:element ref="d:ContentionData" />

        <xs:element ref="d:BlockedTrans" />

      </xs:sequence>

      <xs:attribute name="AbortFlag" type="xs:NCName" use="required" d:item="_AbortFlag_item_" />

      <xs:attribute name="CollectTimeStamp" type="xs:dateTime" use="required" d:item="_CollectTimeStamp_item_" />

      <xs:attribute name="DelayStartTime" type="xs:dateTime" use="required" d:item="_DelayStartTime_item_" />

      <xs:attribute name="QueryID" type="xs:integer" use="required" d:item="_QueryID_item_" />

      <xs:attribute name="TransactionCount" type="xs:integer" use="required" d:item="_TransactionCount_item" />

    </xs:complexType>

  </xs:element>

  <xs:element name="ContentionData">

    <xs:complexType>

      <xs:sequence>

        <xs:element ref="d:BlockerRef"/>

        <xs:element ref="d:BlockedRef"/>

      </xs:sequence>

      <xs:attribute name="GlobalDeadLock" use="required" type="xs:boolean"/>

      <xs:attribute name="LocalDeadLock" use="required" type="xs:boolean"/>

      <xs:attribute name="LockDelay" use="required" type="xs:integer"/>

      <xs:attribute name="MultipleBlocker" use="required" type="xs:boolean"/>

      <xs:attribute name="vproc" use="required" type="xs:integer"/>

    </xs:complexType>

  </xs:element>

  <xs:element name="BlockerRef">

    <xs:complexType>

      <xs:sequence>

        <xs:element ref="d:RequestRef"/>

        <xs:element ref="d:SessionRef"/>

        <xs:element ref="d:LockData"/>

        <xs:element ref="d:LockObject"/>

        <xs:element ref="d:Job"/>

      </xs:sequence>

    </xs:complexType>

  </xs:element>

  <xs:element name="Job">

    <xs:complexType>

      <xs:sequence>

        <xs:element ref="d:Message"/>

        <xs:element ref="d:Operation"/>

      </xs:sequence>

    </xs:complexType>

  </xs:element>

  <xs:element name="Message">

    <xs:complexType>

      <xs:attribute name="MessageClass" use="required" type="xs:integer"/>

      <xs:attribute name="MessageClassName" use="required" type="xs:NCName"/>

      <xs:attribute name="MessageKind" use="required" type="xs:integer"/>

      <xs:attribute name="MessageKindName" use="required" type="xs:NCName"/>

    </xs:complexType>

  </xs:element>

  <xs:element name="Operation">

    <xs:complexType>

      <xs:attribute name="JobType" use="required" type="xs:NCName"/>

      <xs:attribute name="OperationType" use="required"/>

      <xs:attribute name="WorkLevel" use="required" type="xs:integer"/>

    </xs:complexType>

  </xs:element>

  <xs:element name="BlockedRef">

    <xs:complexType>

      <xs:sequence>

        <xs:element ref="d:RequestRef"/>

        <xs:element ref="d:SessionRef"/>

        <xs:element ref="d:LockData"/>

      </xs:sequence>

    </xs:complexType>

  </xs:element>

  <xs:element name="BlockedTrans">

    <xs:complexType>

      <xs:sequence>

        <xs:element ref="d:RequestRef"/>

        <xs:element ref="d:SessionRef"/>

        <xs:element ref="d:LockData"/>

        <xs:element ref="d:LockObject"/>

      </xs:sequence>

      <xs:attribute name="TransactionOrder" use="required" type="xs:integer"/>

    </xs:complexType>

  </xs:element>

  <xs:element name="RequestRef">

    <xs:complexType>

      <xs:sequence minOccurs="0">

        <xs:element ref="d:RequestID"/>

        <xs:element ref="d:Step"/>

        <xs:element ref="d:Transaction"/>

      </xs:sequence>

    </xs:complexType>

  </xs:element>

  <xs:element name="RequestID">

    <xs:complexType>

      <xs:attribute name="RequestID_1" use="required" type="xs:integer"/>

      <xs:attribute name="RequestID_2" use="required" type="xs:integer"/>

    </xs:complexType>

  </xs:element>

  <xs:element name="Step">

    <xs:complexType>

      <xs:attribute name="StatementNo" type="xs:integer"/>

      <xs:attribute name="StepLevel" type="xs:integer"/>

      <xs:attribute name="StepNum_1" use="required" type="xs:integer"/>

      <xs:attribute name="StepNum_2" use="required" type="xs:integer"/>

    </xs:complexType>

  </xs:element>

  <xs:element name="Transaction">

    <xs:complexType>

      <xs:attribute name="TransactionStartTime" use="required" type="xs:dateTime"/>

      <xs:attribute name="TransactionState" use="required" type="xs:NCName"/>

      <xs:attribute name="unique_1" use="required" type="xs:integer"/>

      <xs:attribute name="unique_2" use="required" type="xs:integer"/>

      <xs:attribute name="vproc" use="required" type="xs:integer"/>

    </xs:complexType>

  </xs:element>

  <xs:element name="SessionRef">

    <xs:complexType>

      <xs:sequence>

        <xs:element ref="d:Session"/>

        <xs:element minOccurs="0" ref="d:User"/>

      </xs:sequence>

      <xs:attribute name="LogicalHostId" use="required" type="xs:integer"/>

      <xs:attribute name="ZoneId" use="required" type="xs:integer"/>

    </xs:complexType>

  </xs:element>

  <xs:element name="Session">

    <xs:complexType>

      <xs:attribute name="SessionID_1" use="required" type="xs:integer"/>

      <xs:attribute name="SessionID_2" use="required" type="xs:integer"/>

      <xs:attribute name="SessionID_Combined" use="required" type="xs:integer"/>

    </xs:complexType>

  </xs:element>

  <xs:element name="User">

    <xs:complexType>

      <xs:attribute name="AccountName" type="xs:NCName"/>

      <xs:attribute name="UserName" use="required" type="xs:NCName"/>

    </xs:complexType>

  </xs:element>

  <xs:element name="LockData">

  </xs:element>

  <xs:element name="LockObject">

    <xs:complexType>

      <xs:attribute name="DatabaseName" use="required" type="xs:NCName"/>

      <xs:attribute name="TableName" use="required" type="xs:NCName"/>

    </xs:complexType>

  </xs:element>

</xs:schema>

I have also validated the xml against xsd which comes true still getting same error.

Please help