This discussion is archived
2 Replies Latest reply: Sep 25, 2012 10:33 PM by gauty RSS

XML Parsing

gauty Newbie
Currently Being Moderated
Hi All,

I am running my 11g R2 version of Oracle DB.
I want to parse an XML using either xml extract or dbms_xmlparser functionality.

<soapenv:Envelope xmlns:soapenv="http://abcd.xyz.org/soap/envelope/">
     <soapenv:Header/>
     <soapenv:Body>
          <n0:CreateResponse xmlns:n0="abc:xyz.ws.rn.com/v1_2" xmlns:n2="abc:xyz.ws.rn.com/v1_2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:n1="urn:objects.ws.rightnow.com/v1_2">
               <n0:RNObjectsResult>
                    <n0:RNObjects xsi:type="n1:Contact">
                         <n2:ID id="57"/>
                    </n0:RNObjects>
                    <n0:RNObjects xsi:type="n1:Incident">
                         <n2:ID id="20"/>
                    </n0:RNObjects>
               </n0:RNObjectsResult>
          </n0:CreateResponse>
     </soapenv:Body>
</soapenv:Envelope>

This particular XML is a sample XML that gets generated at a remote location and is sent to our DB as response.
This is the static structure of the XMl that gets sent to us after every transaction.
Can anybody help me with how to extract this XML ? or at least get the Contact ID which is 57 and incident ID which is 20???
  • 1. Re: XML Parsing
    AlexAnd Guru
    Currently Being Moderated
    plz search on forum before post
    forum has many examples
     
    SQL> select * from v$version where rownum=1;
     
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
     
    SQL> 
    SQL> with t as
      2  ( select xmltype('<soapenv:Envelope xmlns:soapenv="http://abcd.xyz.org/soap/envelope/">
      3  <soapenv:Header/>
      4  <soapenv:Body>
      5  <n0:CreateResponse xmlns:n0="abc:xyz.ws.rn.com/v1_2" xmlns:n2="abc:xyz.ws.rn.com/v1_2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:n1="urn:objects.ws.rightnow.com/v1_2">
      6  <n0:RNObjectsResult>
      7  <n0:RNObjects xsi:type="n1:Contact">
      8  <n2:ID id="57"/>
      9  </n0:RNObjects>
     10  <n0:RNObjects xsi:type="n1:Incident">
     11  <n2:ID id="20"/>
     12  </n0:RNObjects>
     13  </n0:RNObjectsResult>
     14  </n0:CreateResponse>
     15  </soapenv:Body>
     16  </soapenv:Envelope>') xml from dual)
     17  --
     18  select x.id, x.typ
     19      from t, xmltable(
     20             xmlnamespaces('http://abcd.xyz.org/soap/envelope/' as "soapenv"
     21                          ,'abc:xyz.ws.rn.com/v1_2' as "n0"
     22                          ,'abc:xyz.ws.rn.com/v1_2' as "n2"
     23                         
     24                          )
     25           , '/soapenv:Envelope/soapenv:Body/n0:CreateResponse/n0:RNObjectsResult/n0:RNObjects'
     26             passing t.xml
     27             columns id varchar2(80) path 'n2:ID/@id'
     28                     , typ varchar2(80) path '@xsi:type'
     29           ) x
     30  /
     
    ID                                                                               TYP
    -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
    57                                                                               n1:Contact
    20                                                                               n1:Incident
     
    SQL> 
  • 2. Re: XML Parsing
    gauty Newbie
    Currently Being Moderated
    Thank you :)
    I was able to get it working..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points