17.10 - Example: Using XMLTABLE With the default_clause - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - XML Data Type

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

This example illustrates the use of the default_clause in column definitions. The path for customerName is modified to one that does not match the document structure. The default value is returned for the customer name.

FROM (SELECT * FROM customer WHERE customerID = 1) AS C,
   XMLTable (
      PASSING C.customerXML
         "Seqno" FOR ORDINALITY,
         "CustomerName" VARCHAR(64) PATH '../../NonExistentNode' DEFAULT 'John Doe',
         "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 Doe       NW-01-16366   12/02/28    001      10       29.50     295.00
         2  John Doe       NW-01-16366   12/02/28    101       1      139.99     139.99