17.00 - Example: Converting a CLOB Containing XML Data into Rows for Insertion into a Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

The following example creates a procedure that converts a CLOB value containing XML sales data into a set of rows that are inserted into a sales table.

First the table definitions:

    CREATE TABLE sales (
      partnum   INTEGER, 
      qtysold   INTEGER, 
      storecode INTEGER, 
      salesdate DATE)
    PRIMARY INDEX (partnum, qtysold, storecode, salesdate);
    CREATE TABLE saleslog (
      storecode INTEGER, 
      salesdate DATE, 
      sales     CLOB, 
      logdate   DATE, 
      logtime   TIME)
    PRIMARY INDEX (storecode, salesdate, logdate, logtime);

The following SQL code defines the procedure:

    CREATE PROCEDURE stores_sales_procedure (storecode INTEGER, 
                                             salesdate DATE, 
                                             salesclob CLOB)
      BEGIN
        INSERT INTO saleslog (:storecode, :salesdate, :salesclob,
                              CURRENT_DATE, CURRENT_TIME);
        INSERT INTO sales 
          SELECT * FROM TABLE (xmlparser(:salesclob));
      END;

The stages in the process followed by this procedure are as follows:

  1. The sales CLOB is inserted into a log table named saleslog.
  2. The CLOB is passed by means of the variable salesclob to a user-defined table function named xmlparser (see CREATE FUNCTION and REPLACE FUNCTION (Table Form)).
  3. The system invokes table function xmlparser on every AMP.
  4. The table function invokes fnc_loblocal (the definitions for xmlparser and fnc_loblocal are not shown for this example) to detect whether the CLOB is located on the same AMP on which the function is executing.
  5. The function that has local access to the CLOB strips out the XML sales data and returns standard relational table rows to be inserted into sales.