Example: Creating and Using an XML Function - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Advanced SQL Engine
Teradata Database
Release Number
September 2020
English (United States)
Last Update
Product Category
Teradata Vantage™

Assume you have the following table function definition:

    CREATE FUNCTION xml_extract( xml_text LOCATOR CLOB)
                    store   INTEGER,
                    item    INTEGER)
     NO SQL

This function extracts all items from the CLOB xml_text that have been ordered from a particular store by a particular customer by means of a web application. It then produces one result row for each item ordered.

The XML data is already in a database table with the following table definition.

    CREATE TABLE xml_tbl (
      store_no INTEGER,
      refnum   INTEGER,
      xml_store_text CLOB)
    UNIQUE PRIMARY INDEX (store_no, refnum);
The assumptions underlie the analysis of the XML data by means of the xml_extract table function.
  • Table xml_tbl contains the following columns.
    • A store number column named store_no.
    • A reference number column named refnum.
    • An XML text column named xml_store_text.
  • The xml_store_text column contains the XML-formatted text for customers who ordered web-based items from the store.
  • Each XML text data column contains information for the customer who placed the order as well as the items that customer ordered.

The purpose of the table function is to extract all items the customer ordered from the XML document. One XML row is created for each order placed by the online web-based system. Because the XML text could consist of several items, a table function is the natural approach to extracting the data, with one row being extracted for each item ordered. If there were 10 items in the XML text, then the table function would return a 10-row table.

The following SELECT request shows a possible application for the xml_extract table function:

     SELECT l.customer_id l.store, l.item,  
     FROM (SELECT xml_store_text
           FROM xml_tbl AS x 
           WHERE store_no = 25), TABLE(xml_extract(x.xml_text_store)) 
                                 AS l (cust_id,store,item);

This SELECT request produces one row for each item bought by all customers from store 25. Its first derived table produces the XML test field from all rows with a store number of 25. The second derived table is the result of evaluating table function xml_extract.

The logical process followed by the SELECT operation is as follows:

  1. Create the first derived table from the first subquery in the FROM clause with the table correlation name x.
  2. Evaluate the table function.

    The function has an input argument that references column x.xml_text_store from the derived table. The database must invoke the table function repeatedly in a loop for each row produced by table x. The loop ends when the table function returns with the “no more data” message.

    The process followed by the loop is as follows:

    1. Read a row for table x where store_no = 25.
    2. Determine whether such a row is found.
      IF a matching row is … THEN …
      found continue processing.
      not found stop processing.
    3. Call the table function xml_extract (x.xml_text_store).
    4. Determine whether there is more data to process.
      IF the call … THEN …
      does not return with a SQLSTATE code of ‘02000’

      (no more data)

      continue processing.
      returns with a SQLSTATE code of ‘02000’ process complete.
    5. Write the new row produced by the xml_extract table function.
    6. Go to Stage c.
  3. Return results to requestor.

Vantage performs the following process when solving this problem:

  1. The system passes the x.xml_text_store result produced by the derived table SELECT request as input to the table function xml_extract.
  2. The system invokes the table function repeatedly for the same row.

    Each time the table function is called, it produces one row containing the cust_id, store , and item columns.

  3. When the table function has exhausted everything from that xml_text_store CLOB, it returns with an SQLSTATE of “no data” (‘02000’).
  4. This “no more data” result causes the system to read the next row produced by the first derived table.
  5. The system repeats the process for another customer, generating more rows for the next xml_text_store.