Example: Shredding an XML Document - 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™

This example shows how you can use the annotated schema defined in the previous example (annotatedcustomerschema.xsd) to shred the sample XML document, Cust001.xml.

The tables referenced in this example are defined as:

CREATE TABLE SHREDMAPPING (
   SCHEMA_ID VARCHAR(32),
   ANNOTATED_SCHEMA XML )
   PRIMARY INDEX (SCHEMA_ID);
CREATE TABLE CUSTOMER (
   customerID INTEGER,
   customerName VARCHAR(256),
   customerXML XML )
   PRIMARY INDEX (customerID);
  1. Load the annotated schema into the shredmapping table: 
    .IMPORT VARTEXT '|' LOBCOLS=1 FILE=annotatedschema.txt
USING (asxsd XML AS DEFERRED, schemaid varchar(32))
INSERT into SHREDMAPPING values(:schemaid, :asxsd);

    The contents of the annotatedschema.txt file are:

    annotatedcustomerschema.xsd|annotatedcustomerschema.xsd

    Here are partial contents of the shredmapping table after the INSERT operation:

    SCHEMA_ID                        ANNOTATED_SCHEMA
-------------------------------- ------------------------------------------------------
annotatedcustomerschema.xsd      <?xml version="1.0" encoding="UTF-8" ?> <xs:schema...
  2. Load the XML document into the customer table: 
    .IMPORT VARTEXT '|' LOBCOLS=1 FILE='custdocs.txt'
USING (custdoc XML AS DEFERRED, custid VARCHAR(256), custname VARCHAR(256))
INSERT INTO CUSTOMER(CAST(:custid AS INTEGER), :custname, :custdoc);

    The contents of the custdocs.txt file are:

    Cust001.xml|1|John Hancock
  3. Define an SQL query that returns a result set with 2 columns: an ID column and the XML column that contains the XML document to be shredded: 
    SELECT customerID, customerXML FROM CUSTOMER;

    Here are partial results from the query. The ellipsis (...) is not part of the query results. It indicates that the query returns additional results that are truncated in the example.

    customerID customerXML
----------- --------------------------------------------------------------
          1 <?xml version="1.0" encoding="UTF-8" ?> <Customer>  <Name>John...
  4. Invoke the AS_SHRED_BATCH stored procedure with the following arguments:
    • The SQL query you defined in step 3
    • The annotated schema from step 1

      The annotated schema now available in the SHREDMAPPING table as a result of step (1) needs to be made available to the shredding stored procedure. One way to do this is to write a wrapper stored procedure:

      REPLACE PROCEDURE MY_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 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;

      This stored procedure takes two parameters:

      1. An SQL query that retrieves all the documents to be shredded
      2. An ID for the annotated schema (the key for the SHREDMAPPING table)

      In the stored procedure, we first retrieve the annotated schema and then use it in the call to the AS_SHRED_BATCH stored procedure to shred the documents.