Example: XQuery/XPath Query Returns Multiple Atomic Values Cast to the DECIMAL Data Type - Advanced SQL Engine - Teradata Database

The following query returns multiple atomic values cast to the DECIMAL data type:

SELECT CAST(x.item AS DECIMAL(16,0))
FROM xmltab,
XMLTABLE(
   'for $cust in /customers/customer return sum(order/total)'
   passing xmltab.xmldoc as "cust"
   COLUMNS "item" XML PATH ".") x;

An alternative way to write this query is as follows:

SELECT x.item
FROM xmltab,
XMLTABLE(
   'for $cust in /customers/customer return sum(order/total)'
   passing xmltab.xmldoc as "cust"
   COLUMNS "item" DECIMAL(16,0) PATH ".") x;

Row #1:

   2496.50

Row #2:

   1383.50