我在包含SUM
和COUNT
函数的Oracle(11g R2)中运行一些相当简单的-xPath查询时遇到问题。例如:
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;
与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;
我得到了ORA-31012
的回应。知道如何让它工作吗?或者有什么可能的变通办法?
我不记得上一次使用extractvalue
是什么时候了,因为它在11g中被取消了。相反,我使用xmlquery
:
下面是一个如何使用count
和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
;
希望这能有所帮助!
我在没有使用as "doc"
和删除$doc
的情况下尝试了@user272735的答案,结果似乎是一样的:
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
;