Example: Converting XML Documents to Rows and Columns

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

The XMLTABLE function can convert one or more XML documents into rows and columns. It takes as input a row query expression which generates a sequence of nodes. Each sequence of nodes results in a row. A set of column query expressions determines how each of the column values is computed from the nodes.

This example finds all the "item" elements in the input XML documents and for each element found, a row is constructed with the column values constructed from the descendant elements and attributes of that element, such as ItemID or quantity.

SELECT X.*
FROM (SELECT * FROM customer WHERE customerID = 1) AS C,
   XMLTable (
      '/Customer/Order/Item'
      PASSING C.customerXML
      COLUMNS
         "Seqno" FOR ORDINALITY,
         "CustomerName" VARCHAR(64) PATH '../../Name',
         "OrderNumber" VARCHAR(32) PATH '../@Number',
         "OrderDate" DATE PATH '../@Date',
         "ItemID" VARCHAR(12) PATH '@ID',
         "Quantity" INTEGER PATH 'Quantity',
         "UnitPrice" DECIMAL(9,2),
         "TotalPrice" DECIMAL(9,2) PATH 'Price'
   ) AS X ("Sequence #", "Customer Name", "Order #", "Order Date", "Item ID", "Qty", "Unit Price", "Total Price");

The result of the query is:

Sequence# Customer Name   Order#      Order Date    Item ID  Qty  Unit Price  Total Price
----------------------------------------------------------------------------------------
        1  John Hancock   NW-01-16366   12/02/28        001   10       29.50      295.00
        2  John Hancock   NW-01-16366   12/02/28        101    1      139.99      139.99