2 Replies Latest reply: Sep 26, 2012 12:33 AM by gauty RSS

    XML Parsing

    gauty
      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
          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
            Thank you :)
            I was able to get it working..