Home » Developer & Programmer » JDeveloper, Java & XML » how to extract hierarchy xml into oracle relational table
how to extract hierarchy xml into oracle relational table [message #412789] Sat, 11 July 2009 19:25 Go to next message
zhefeng
Messages: 4
Registered: July 2009
Junior Member
i have a xmltype table like this:
SQL> select object_value from tbl_testxml;
<members>
<grandfather name="Tom" age="66">
<father name="John" age="46">
<son name="Simon" age="20"/>
<son name="Steve" age="19"/>
</father>
<father name="Jeff" age="45">
<son name="Mark" age="17"/>
<son name="Mathiew" age="15"/>
</father>
</grandfather>
</members>

What i want to do is extract the grandfather, father and son's name (in the same line if they are in same family), so i use this sql query:

select extract(value(v_xml),'//grandfather/@name') as grandfather,
extract(value(v_xml),'//grandfather/father/@name') as father,
extract(value(v_xml),'//grandfather/father/son/@name') as son
from tbl_testxml t,
TABLE (XMLSEQUENCE (EXTRACT (t.object_value, '/members'))) v_xml;

The output is like this eventually:
grandfather     father         son
========        ==========     ================
Tom             JohnJeff       SimonSteveMarkMathiew

But this is not what i want, what i want is like this:
grandfather    father     son
========       ======     ======
Tom            John       Simon
Tom            John       Steve
Tom            Jeff       Mark
Tom            Jeff       Mathiew

is that possible to get the result as above?

i've tried xmlquery, xmltable etc, none of them works Sad

Thanks so much!

[Updated on: Sat, 11 July 2009 19:27]

Report message to a moderator

Re: how to extract hierarchy xml into oracle relational table [message #412801 is a reply to message #412789] Sun, 12 July 2009 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 59421
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with 
  2    data as (
  3      select xmltype(
  4  '<members>
  5    <grandfather name="Tom" age="66">
  6      <father name="John" age="46">
  7        <son name="Simon" age="20"/>
  8        <son name="Steve" age="19"/>
  9      </father>
 10      <father name="Jeff" age="45">
 11         <son name="Mark" age="17"/>
 12         <son name="Mathiew" age="15"/>
 13      </father>
 14    </grandfather>
 15  </members>') val from dual
 16    )
 17  select extractvalue(gf.column_value,'/grandfather/@name') grandfather,
 18         extractvalue(f.column_value,'/father/@name') father,
 19         extractvalue(s.column_value,'/son/@name') son
 20  from data t,
 21       table(xmlsequence(extract(t.val, '/members/grandfather'))) gf,
 22       table(xmlsequence(extract(gf.column_value, '/grandfather/father'))) f,
 23       table(xmlsequence(extract(f.column_value, '/father/son'))) s
 24  /
GRANDFATHER          FATHER               SON
-------------------- -------------------- ----------------
Tom                  John                 Simon
Tom                  John                 Steve
Tom                  Jeff                 Mark
Tom                  Jeff                 Mathiew

4 rows selected.

Regards
Michel
Re: how to extract hierarchy xml into oracle relational table [message #412829 is a reply to message #412789] Sun, 12 July 2009 19:06 Go to previous message
zhefeng
Messages: 4
Registered: July 2009
Junior Member
Amazing, that's exact what i want!

Thanks you so much Michel!
Previous Topic: How to send sms from Oracle 10g Devsuite forms
Next Topic: EJB object - Getting Null Pointer Exception (merged 4)
Goto Forum:
  


Current Time: Wed Oct 22 17:10:11 CDT 2014

Total time taken to generate the page: 0.09182 seconds