17.10 - Example: XQuery/XPath Query Uses XMLTABLE to Retrieve Items From a Sequence - 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)
The following query uses XMLTABLE to retrieve items from a sequence:
SELECT x.item
FROM xmltab,
          passing xmltab.xmldoc as "custXML"
          COLUMNS "item" XML PATH "."
         ) AS x;

The result of this query is multiple rows, each containing a single customer element in the item column.

Row #1:
   <Name>John Hancock</Name>
   <Address>100 1st Street One City, CA 12345</Address>
   <order Number="NW-01-16366" Date="Feb/28/2001">
      <Contact>Mary Shannon</Contact>
      <ShipTo>Widgets Inc., 123 Regency Parkway, Portland, OR 43211</ShipTo>
      <item ID="001">
Row #2:
   <Name>Jim Smith</Name>
   <Address>200 2nd Street, San Diego, CA 12345</Address>
   <order Number="JS-01-16366" Date="Feb/29/2001">
      <Contact>Jim Smith</Contact>
      <ShipTo>Acme co., 2467 Pioneer Road, San Diego, CA 12345</ShipTo>
      <item ID="001">