3 Replies Latest reply: Oct 10, 2012 3:10 PM by 879994 RSS

    Extracting repeating elements from XML

    879994
      In continuation of thread How can I get the elements from XML (Since this thread already answered, so I am opening a new thread)

      Database information
      BANNER
      ----------------------------------------------------------
      Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
      PL/SQL Release 10.2.0.3.0 - Production
      CORE    10.2.0.3.0      Production
      TNS for Linux: Version 10.2.0.3.0 - Production
      NLSRTL Version 10.2.0.3.0 - Production
      XML
      <rdf:RDF xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns:skos="http://www.w3.org/2004/02/skos/core#">
        <skos:Concept rdf:about="http://my.site.com/#Acoustics">
          <skos:ID>12033</skos:ID>
         <skos:narrower rdf:resource="http://my.site.com/#Aeroacoustics"/>
          <skos:narrower rdf:resource="http://my.site.com/#Vibrations (Acoustics)"/>
          <skos:narrower rdf:resource="http://my.site.com/#Acoustic materials"/>
         </skos:Concept>
       </rdf:RDF>
      I want result as follows
      About                ID                          Resource
      
      Acoustics           12033                      Aeroacoustics
      Acoustics           12033                      Vibrations (Acoustics)
      Acoustics           12033                      Acoustic materials
      Any help would be much appreciated.
      Thanks in advance.

      Edited by: 876991 on Oct 10, 2012 11:26 AM

      Edited by: 876991 on Oct 10, 2012 11:28 AM
        • 1. Re: Extracting repeating elements from XML
          Jason_(A_Non)
          Here is a start, based on what odie_63 had already provided.
          SELECT x.about, x.id, x2.res
            FROM th_xml t
               , xmltable(
                   xmlnamespaces(
                     'http://www.w3.org/1999/02/22-rdf-syntax-ns#' as "rdf"
                   , 'http://www.w3.org/2004/02/skos/core#' as "skos"
                   )
                 , '/rdf:RDF/skos:Concept'
                   passing t.in_file_1
                   columns
                     about   varchar2(30) path '@rdf:about'
                   , ID      number       path 'skos:ID'
                   , nar_xml xmltype      path 'skos:narrower') x
               , xmltable(
                   xmlnamespaces(
                     'http://www.w3.org/1999/02/22-rdf-syntax-ns#' as "rdf"
                   , 'http://www.w3.org/2004/02/skos/core#' as "skos"
                   )
                 , 'skos:narrower'
                   passing x.nar_xml
                   columns
                     res      varchar2(50)       path '@rdf:resource') x2
          • 2. Re: Extracting repeating elements from XML
            odie_63
            Or the shorter version :
            select x1.*
            from th_xml t
               , xmltable(
                   xmlnamespaces(
                     'http://www.w3.org/1999/02/22-rdf-syntax-ns#' as "rdf"
                   , 'http://www.w3.org/2004/02/skos/core#' as "skos"
                   )
                 , 'for $i in /rdf:RDF/skos:Concept
                      , $j in $i/skos:narrower
                    return element r {
                      element about { substring-after($i/@rdf:about,"#") }
                    , $i/skos:ID
                    , element resource { substring-after($j/@rdf:resource,"#") }
                    }'
                   passing t.in_file_1
                   columns
                     About  varchar2(40) path 'about'
                   , ID     number       path 'skos:ID'
                   , Resrc  varchar2(40) path 'resource'
                 ) x1
            ;
            • 3. Re: Extracting repeating elements from XML
              879994
              Thank you A_non & odie_63. You guys are excellent !!!