XMLCAST in oracle

Question

I have a table with an xml column and trying to do sum of values in an xml tag.

Table created: CREATE TABLE XML_TABLE6 ( XML_COL VARCHAR2(2000 BYTE) );

Insert into XML_TABLE6
   (XML_COL)
 Values
   ('<a><b>1</b><b>2</b></a>');
COMMIT;

I am using the below select statement to return the expression in datatype "double". But i am getting the error "ORA-00905: missing keyword".

SQL query:

select XMLCast(XMLQuery('sum(a/b)' RETURNING CONTENT) as double) from xml_table6;

Expected output: 3.0

Answer 1

In Oracle, the ANSI data type is double precision, not just double.

You also need to pass in the actual column value, and as that's a string, convert it to XMLType:

select
  XMLCast(
    XMLQuery('sum(a/b)' PASSING XMLType(xml_col) RETURNING CONTENT)
    as double precision)
from xml_table6;

Or use a normal number data type:

select 
  XMLCast(
    XMLQuery('sum(a/b)' PASSING XMLType(xml_col) RETURNING CONTENT)
    as number
   )
from xml_table6;

Or binary_double:

select 
  XMLCast(
    XMLQuery('sum(a/b)' PASSING XMLType(xml_col) RETURNING CONTENT)
    as binary_double
   )
from xml_table6;

db<>fiddle

Answer 2

There are some issues in the query:

• You didn't specify the column you want to take as input (XML_passing_clause)
• You need to explicitly cast the column to the XMLType instance to process it with XML functions.
• Oracle doesn't have double data type. See numeric data types in the documentation. XMLCAST function:

The datatype argument can be of data type NUMBER, VARCHAR2, CHAR, CLOB, BLOB, REF XMLTYPE, and any of the datetime data types.

After you've fixed this issues, it works fine:

with XML_TABLE6(XML_COL) as (
  select '<a><b>1</b><b>2</b></a>'
  from dual
)
select xmlcast(
    XMLQuery('sum(a/b)' passing xmltype(XML_COL) RETURNING CONTENT)
    as binary_double
  ) as res
from XML_TABLE6

|RES|
|:--|
|3.0E+000|

db<>fiddle