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