3 Replies Latest reply: Jan 29, 2013 7:39 AM by odie_63 RSS

    Parsing XML

    Nimish Garg
      Hi All,

      I have following XML stored in a clob filed on Oracle 11g R2 Database.
      <?xml version="1.0" encoding="UTF-8"?>
      <!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
      <plist version="1.0">
      <dict>
            <key>CommandUUID</key>
            <string>512</string>
            <key>ManagedApplicationList</key>
            <dict>
                 <key>com.kbb.valuesapp</key>
                  <dict>
                        <key>ManagementFlags</key>
                      <integer>5</integer>
                        <key>Status</key>
                      <string>Managed</string>
                  </dict>
                  <key>com.madebypharaohs.awesomefactspro2</key>
                  <dict>
                        <key>ManagementFlags</key>
                        <integer>1</integer>
                        <key>Status</key>
                        <string>UserRejected</string>
                        <key>UnusedRedemptionCode</key>
                      <string>N4MN7HTMFAXM</string>
                  </dict>
            </dict>
            <key>Status</key>
            <string>Acknowledged</string>
            <key>UDID</key>
            <string>0ed195a1c4372e685923ebee3a058d1769d81d7c</string>
      </dict>
      </plist>
      I want following output, please guide
      APP_ID          APP_NAME                         KEY               VALUE
      ------------------------------------------------------------------------------------------------------
      1          com.kbb.valuesapp                    ManagementFlags          5
      1          com.kbb.valuesapp                    Status               Managed
      2          com.madebypharaohs.awesomefactspro2          ManagementFlags          1
      2          com.madebypharaohs.awesomefactspro2          Status               UserRejected
      2          com.madebypharaohs.awesomefactspro2          UnusedRedemptionCode     N4MN7HTMFAXM
      Thanks & Regards
      Nimish Garg
        • 1. Re: Parsing XML
          odie_63
          Hi,

          This file is not very user-friendly. We have to use following-sibling axes to read the key-value pairs correctly.

          But first of all, let's deal with the DTD declaration.
          By default Oracle will try to validate the XML as per the DTD definition, but in this case, since the definition is stored externally we get this error :
          ORA-31020: The operation is not allowed, Reason: For security reasons, ftp and http access over XDB repository is not allowed on server side
          The first solution is to disable the validation at session level by setting this event :
          alter session set events '31156 trace name context forever, level 2';
          The alternative is to remove the DOCTYPE node from the CLOB :
          regexp_replace(my_clob, '<!DOCTYPE[^>]+>')
          Choose the option that better fits your need and constraints.


          You can eventually use the following query to get the expected output :
          SQL> alter session set events '31156 trace name context forever, level 2';
           
          Session altered
           
          SQL> 
          SQL> select app.app_id, app.app_name, k.*
            2  from my_table t
            3     , xmltable(
            4         'for $appList in /plist/dict/key[.="ManagedApplicationList"]
            5            , $app in $appList/following-sibling::dict[1]/key
            6          return element r {
            7            $app/following-sibling::dict[1]
            8          , $app
            9          }'
           10         passing xmlparse(document t.my_clob)
           11         columns app_id   for ordinality
           12               , app_name varchar2(40) path 'key'
           13               , keys     xmltype      path 'dict'
           14       ) app
           15     , xmltable(
           16         'for $key in /dict/key
           17          return element r {
           18            element key_value {data($key/following-sibling::*[1])}
           19          , $key
           20          }'
           21         passing app.keys
           22         columns key_name  varchar2(30) path 'key'
           23               , key_value varchar2(30) path 'key_value'
           24       ) k
           25  ;
           
              APP_ID APP_NAME                                 KEY_NAME                       KEY_VALUE
          ---------- ---------------------------------------- ------------------------------ ------------------------------
                   1 com.kbb.valuesapp                        ManagementFlags                5
                   1 com.kbb.valuesapp                        Status                         Managed
                   2 com.madebypharaohs.awesomefactspro2      ManagementFlags                1
                   2 com.madebypharaohs.awesomefactspro2      Status                         UserRejected
                   2 com.madebypharaohs.awesomefactspro2      UnusedRedemptionCode           N4MN7HTMFAXM
           
          • 2. Re: Parsing XML
            Nimish Garg
            Thanks for the solution

            I was planning to solve it by using XMLSequence in following way, but it is making crossjoin. can you plz suggest how to join these/
            SELECT 
                 extractvalue(value(x2),'/key') appname,
                x3.*
            from 
                 my_table,
                 table(XMLSequence(extract(XMLTYPE(substr(my_clob,instr(my_clob,'<plist version="1.0">'))), '/plist/dict/dict'))) x1 ,  
                 table(XMLSequence(extract(value(x1), '/dict/key'))) x2,
                 table(XMLSequence(extract(value(x1), '/dict[key="' || extractvalue(value(x2),'/key') || '"]/dict'))) x3
            Edited by: Nimish Garg on Jan 29, 2013 7:09 PM
            • 3. Re: Parsing XML
              odie_63
              Nimish,

              Sorry, I won't suggest anything regarding your current attempt as it is both a wrong approach and uses deprecated functions.
              Use XMLTable.