search xml for value in oracle

I need to search a table msg_xmldata column msg_data that contains xml data and check a condition

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

I've tried msg_data.getstringval() like '%AoC%' with no luck :(

Please help.

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-开发者_如何学Go30 00:00:01', 'YYYY-MM-DD HH24:MI:SS');

Use the EXTRACTVALUE function to retrieve any part of the XML using an XPath predicate. Something like this:

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');

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

Use of EXTRACTVALUE is deprecated since 11gR2. Use of XMLTABLE or XMLQUERY is recommended instead, but 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');