Example: Converting XML Documents to Rows and Columns - Teradata Vantage NewSQL Engine - 16.20

Teradata Vantageā„¢ XML Data Type

Teradata Database
Teradata Vantage NewSQL Engine
Programming Reference

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.

FROM (SELECT * FROM customer WHERE customerID = 1) AS C,
   XMLTable (
      PASSING C.customerXML
         "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