Ckeck the presence of xml tag using oracle

Question

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

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

I wanted to find if "wbi:value" tag exists in this xml or not ?

I tried using existsnode but in sql developer it is saying an error as to declare existsnode.

Answer 1

yes use existsnode:

SQL> with yourdata as (select to_clob('<wbi:event xmlns:wbi="http://foo" xmlns:xsi="http://x" xmlns:xsd="http://d">
  2  <wbi:appData>
  3      <wbi:content wbi:name="1st_status">
  4          <wbi:value xsi:type="xsd:string">Success</wbi:value>
  5          </wbi:content>
  6      <wbi:content wbi:name="2nd_status">
  7          <wbi:value xsi:type="xsd:string">Failure</wbi:value>
  8          </wbi:content>
  9      </wbi:appData>
 10  </wbi:event>') c from dual)
 11  select existsnode(xmltype(c), '/wbi:event/wbi:appData/wbi:content','xmlns:wbi="http://foo"')  is_exist
 12    from yourdata t
 13  /

  IS_EXIST
----------
         1

ie

existsnode(xmltype(c), '/wbi:event/wbi:appData/wbi:content','xmlns:wbi="http://foo"')

1 = exists 0 = does not exist.

note that in my sample, i had two matching nodes (as i didn't filter on wbi:name). you can filter the xpath of course. eg:

/wbi:event/wbi:appData/wbi:content[@wbi:name="1st_status"]

to limit matches to the "1st_status" one