Example: Shredding an XML Document

Teradata Vantageā„¢ XML Data Type

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1140-162K

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.