XMLCAST in oracle-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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