17.10 - Example: Converting XML Documents to Rows and Columns - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - XML Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1140-171K
Language
English (United States)

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