| how to extract hierarchy xml into oracle relational table [message #412789] |
Sat, 11 July 2009 19:25  |
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 
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  |
 |
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
