Example: XQuery/XPath Query Returns Multiple Atomic Values Cast to the DECIMAL Data Type - Analytics Database - Teradata Vantage

XML Data Type

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2023-10-30
dita:mapPath
tkc1628112506748.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
dgs1472251600184
lifecycle
latest
Product Category
Teradata Vantageā„¢
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;

Both queries return the same results: each row returned will contain a single DECIMAL value.

Row #1:
   2496.50
Row #2:
   1383.50