在oracle中搜索xml以获取值

Question

我需要搜索包含xml数据的表msg_xmldata列msg_data，并检查一个条件

<ns1:aoc-done>1</ns1:aoc-done>

我尝试过像'%AoC%‘这样的msg_data.getstringval()，但没有成功:(

请帮帮忙。

XML

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns1:charge-request xmlns:ns1="api">
    <ns1:spec-ver>1.0</ns1:spec-ver>
    <ns1:transaction-id>2315176</ns1:transaction-id>
    <ns1:timestamp>1203045318</ns1:timestamp>
    <ns1:content-info>
        <ns1:rating-level>99999</ns1:rating-level>
    </ns1:content-info>
    <ns1:aoc-flag>false</ns1:aoc-flag>
    <ns1:optional-charge-params>
        <ns1:price>
            <ns1:amount>3.0</ns1:amount>
        </ns1:price>
    </ns1:optional-charge-params>
    <ns1:success>true</ns1:success>
    <ns1:aoc-done>1</ns1:aoc-done>
</ns1:charge-request>


select x.msg_data.getclobval()
from ccgw_msg_xmldata x
where x.msg_data.getstringval() like %1%
and x.msg_time < to_date('2011-06-30 00:00:01', 'YYYY-MM-DD HH24:MI:SS');

Answer 1

使用EXTRACTVALUE函数检索使用XPath谓词的XML的任何部分。如下所示：

SELECT x.msg_data.getclobval()
FROM   ccgw_msg_xmldata x
WHERE  EXTRACTVALUE(x.msg_data, '/ns1:charge-request/ns1:aoc-done') = 1
AND    x.msg_time < to_date('2011-06-30 00:00:01', 'YYYY-MM-DD HH24:MI:SS');

文档：http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28369/xdb04cre.htm#BABDGFFH

Answer 2

从11gR2开始，EXTRACTVALUE的使用是deprecated。建议使用XMLTABLE或XMLQUERY，但使用YMMV。

SELECT x.msg_data
FROM   ccgw_msg_xmldata x,
       xmltable(xmlnamespaces(default 'api'),
       '/charge-request'
       passing x.msg_data
       columns
       aocdone number path 'aoc-done') x2
WHERE  x2.aocdone = 1
AND    x.msg_time < to_date('2011-06-30 00:00:01', 'YYYY-MM-DD HH24:MI:SS');

SELECT
  xmlquery('declare default element namespace "api"; (: :)
            for $aocdone in $doc/charge-request/aoc-done
            where $aocdone = 1
            return $doc'
           passing x.msg_data as "doc"
           returning content)
FROM ccgw_msg_xmldata x
WHERE x.msg_time < to_date('2011-06-30 00:00:01', 'YYYY-MM-DD HH24:MI:SS');