This discussion is archived
9 Replies Latest reply: Oct 10, 2012 1:11 PM by 879994 RSS

How can I get the elements from XML

879994 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks odie_63, You are great. Much appreciated your help.
  • 5. Re: How can I get the elements from XML
    879994 Newbie
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you odie_63. It helps a lot and I really appreciate your help.

Legend

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