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