SUM and COUNT xPath expression doesn't work in Oracle 11.2

Question

I have a problem running some - rather simple - xPath queries in Oracle (11g R2) containing SUM and COUNT functions. For example:

select extractvalue(
                    xmltype.createxml('<a>
                                         <b>
                                           <c>1</c>
                                           <d>XXX</d>
                                         </b>
                                         <b>
                                           <c>2</c>
                                           <d>YYY</d>
                                         </b>
                                       </a>')
                    , 'sum(/a/b/c)'
                    ) 
  from dual;

same for count:

select extractvalue(
                    xmltype.createxml('<a>
                                         <b>
                                           <c>1</c>
                                           <d>XXX</d>
                                         </b>
                                         <b>
                                           <c>2</c>
                                           <d>YYY</d>
                                         </b>
                                      </a>')
                   , 'count(/a/b/c)'
                   ) 
   from dual;

I am getting ORA-31012 in response. Any idea how to get it working? Or what could be a possible workaround?

Answer 1

I don't recall the last time I've been using extractvalue as it is depracated in 11g. Instead I'm using xmlquery:

Here's a simple example how to count and sum:

with 
xmldata as (
  select xmltype('<a><b><c>1</c><d>TEXT1</d></b><b><c>2</c><d>TEXT2</d></b></a>') as data_ from dual
)
select 'COUNT', xmlquery('
count($doc/a/b/c)
' passing data_ as "doc" returning content) as result_
from xmldata
union all
select 'SUM', xmlquery('
sum($doc/a/b/c)
' passing data_ as "doc" returning content) as result_
from xmldata
;

Hope this helps !

Answer 2

I tried the answer from @user272735 without using as "doc" and removing $doc and it seems to work the same:

with 
xmldata as (
  select xmltype('<a><b><c>1</c><d>TEXT1</d></b><b><c>2</c><d>TEXT2</d></b></a>') as data_ from dual
)
select 'COUNT', xmlquery('
count(/a/b/c)
' passing data_ returning content) a as s result_
from xmldata
union all
select 'SUM', xmlquery('
sum(/a/b/c)
' passing data_ returning content) as result_
from xmldata
;