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

    How can I get the elements from XML

    879994
      Hi,

      I do have following sample XML file and I want to get the elements of this XML. How can I get the XML elements?

      Database Information
      SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 24 17:17:55 2012
       
      Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
       
       
      Connected to:
      Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
       
      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:NO>150</skos:NO>
          <skos:ANO>232</skos:ANO>
          <skos:prefLabel>Acoustics</skos:prefLabel>
          <skos:historyNote>http://www.theiet.org/resources/inspec/</skos:historyNote>
          <skos:altLabel rdf:resource="http://my.site.com/#Sound"/>
          <skos:broader rdf:resource="http://my.site.com/#Physics"/>
          <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:THES>PHYS</skos:THES>
          <skos:STA>Candidate</skos:STA>
        </skos:Concept>
        <skos:Concept rdf:about="http://my.site.com/#Sound">
          <skos:NO>450</skos:NO>
          <skos:ID>1483636</skos:ID>
          <skos:USE>Acoustics</skos:USE>
        </skos:Concept>
      </rdf:RDF>
      Thanks in advance.

      Edited by: 876991 on Jul 26, 2012 12:02 PM
        • 1. Re: How can I get the elements from XML
          odie_63
          Hi,

          Following up on your previous thread : {thread:id=2418847},
          I assume you've successfully loaded the file in the TH_XML table (or similar).

          The general approach is like this :
          SQL> select x.*
            2  from th_xml t
            3     , xmltable(
            4         xmlnamespaces(
            5           'http://www.w3.org/1999/02/22-rdf-syntax-ns#' as "rdf"
            6         , 'http://www.w3.org/2004/02/skos/core#' as "skos"
            7         )
            8       , '/rdf:RDF/skos:Concept'
            9         passing t.in_file_1
           10         columns
           11           ID   number       path 'skos:ID'
           12         , NO   number       path 'skos:NO'
           13         , ANO  number       path 'skos:ANO'
           14         , USE  varchar2(30) path 'skos:USE'
           15       ) x
           16  ;
           
                  ID         NO        ANO USE
          ---------- ---------- ---------- ------------------------------
               12033        150        232 
             1483636        450            Acoustics
           
          You can add the other singleton elements in the same way.
          However, if you want to extract repeating nodes such as <skos:narrower>, you have to decide how you want the output to look like (nested collection etc.).
          • 2. Re: How can I get the elements from XML
            879994
            Thanks odie_63, appreciate your quick response. The mention query is running fine without <skos:narrower>. However, if I need to include "skos:narrower" elements then what I need to do? Do I need to write PL/SQL code for that and separately get this data ? or I can get repeating elements in the SQL query? Please help.
            • 3. Re: How can I get the elements from XML
              odie_63
              However, if I need to include "skos:narrower" elements then what I need to do? Do I need to write PL/SQL code for that and separately get this data ? or I can get repeating elements in the SQL query?
              I knew you were going to ask this, that's why I anticipated in my previous reply :
              However, if you want to extract repeating nodes such as <skos:narrower>, you have to decide how you want the output to look like (nested collection etc.).
              So, how do you want to store repeating elements?

              For example, you can use a collection type :
              create type narrower_t is object ( res varchar2(2000) );
              /
              
              create type narrower_coll is table of narrower_t;
              /
              
              select x.ID
                   , x.NO
                   , cast(
                       multiset(
                         select res
                         from 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.narrowers
                                columns res varchar2(2000) path '@rdf:resource'
                              )
                       )
                       as narrower_coll
                     ) as narrowers
              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
                       ID         number       path 'skos:ID'
                     , NO         number       path 'skos:NO'
                     , narrowers  xmltype      path 'skos:narrower'
                   ) x
              ;
              • 4. Re: How can I get the elements from XML
                879994
                Thanks odie_63, You are great. Much appreciated your help.
                • 5. Re: How can I get the elements from XML
                  879994
                  Hi,

                  I am sorry for opening old thread. Actually, I am not able to process nested table through PL/SQL. Can anyone help ?

                  Thanks in advance.
                  • 6. Re: How can I get the elements from XML
                    Jason_(A_Non)
                    Actually, I am not able to process nested table through PL/SQL.
                    In order for someone to provide a relevant answer, we would need to know the details behind that statement. Is there a business reason, an ORA error, something else?
                    • 7. Re: How can I get the elements from XML
                      879994
                      Thanks for reply
                      I am trying to get the nested table element as follows:-
                      declare
                        TYPE t_narrower_term IS TABLE OF narrower_coll ;
                          l_narrower_term          t_narrower_term;
                       begin    
                       select 
                          cast(
                               multiset(
                                 select res
                                 from 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.narrowers
                                        columns res varchar2(2000) path '@rdf:resource'
                                      )
                               )
                               as narrower_coll
                             ) as narrowers bulk collect into l_narrower_term
                      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
                               ID         number       path 'skos:ID'
                             , NO         number       path 'skos:NO'
                             , narrowers  xmltype      path 'skos:narrower'
                           ) x;
                         --loop here    
                         for i in l_narrower_term.first..l_narrower_term.last
                         loop
                         dbms_output.put_line(l_narrower_term(i));
                         end loop;
                      end;
                      But getting following error

                      ORA-06550: line 37, column 4:
                      PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
                      ORA-06550: line 37, column 4:
                      PL/SQL: Statement ignored
                      • 8. Re: How can I get the elements from XML
                        odie_63
                        Your local type "T_NARROWER_TERM" is a collection of collections.
                        Therefore you need to use two nested loops in order to drill down to the value.

                        Something like this :
                        for i in 1 .. l_narrower_term.count loop
                          for j in 1 .. l_narrower_term(i).count loop
                            dbms_output.put_line(l_narrower_term(i)(j).res);
                          end loop;
                        end loop;
                        • 9. Re: How can I get the elements from XML
                          879994
                          Thank you odie_63. It helps a lot and I really appreciate your help.