Read xml tag value from oracle using sql developer

Question

<wbi:appData>
    <wbi:content wbi:name="1st_status">
        <wbi:value xsi:type="xsd:string">Success</wbi:value>
        </wbi:content>
    </wbi:appData>
</wbi:event>

I need to get the value "1st_status" which is in 2nd line and "Success" which is in 3rd line using SQL developer.

this xml is in a table which has a column in the form of CLOB type.

Solution

you do it like this:

(ie table yourdata contains a clob column c)

SQL> select extractvalue(xmltype(c), '/wbi:event/wbi:appData/wbi:content/@wbi:name','xmlns:wbi="http://foo"') name,
  2         extractvalue(xmltype(c), '/wbi:event/wbi:appData/wbi:content/wbi:value','xmlns:wbi="http://foo"') status
  3    from yourdata
  4  /

NAME            STATUS
--------------- ---------------
1st_status      Success

this assumes you wbi namespace is xmlns:wbi="http://foo"

if //content is a repeating tag, then you do this instead:

SQL> select extractvalue(value(t), '/wbi:content/@wbi:name','xmlns:wbi="http://foo"') name,
 2         extractvalue(value(t), '/wbi:content/wbi:value','xmlns:wbi="http://foo"') status
 3    from yourdata,
 4         table(xmlsequence(extract(xmltype(c), '/wbi:event/wbi:appData/wbi:content', 'xmlns:wbi="http://foo"'))) t
 5
SQL> /

NAME            STATUS
--------------- ---------------
1st_status      Success
2nd_status      Failure