Home » Developer & Programmer » JDeveloper, Java & XML » XML structure into regular table (Oracle 11.2.0.3 Windows )
| XML structure into regular table [message #649661] |
Sat, 02 April 2016 09:10  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi all,
I've been asked to perform a task related to xml structure, and after trying to read some documentation related to this issue,
I've been quit overwhelmed and frustrated that I couldn't find any info that was fully understandable to me.
So I'm trying to practice my way in small steps to try and understand how this works, as simple as possible.
What happens is that I receive a xml structure ( not a file ) from some source, like this:
<note>
<userID>13300</userID>
<to_col>Tove</to_col>
<from_col>Jani</from_col>
<contents>bla bla bla bla bla</body>
<i_numbers_related> 455,456,457,458,459</i_numbers_related>
</note>
my env is:
C:\Users\Userpc>sqlplus oe/oe
SQL*Plus: Release 11.2.0.3.0 Production on Sat Apr 2 17:04:49 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set lines 100
SQL> desc notes;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
USERID NUMBER(10)
TO_COL VARCHAR2(15)
FROM_COL VARCHAR2(15)
CONTENTS VARCHAR2(255)
SQL> desc user_to_numbers;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
USERID NUMBER(10)
NUMBER_RELATED NUMBER(10)
I want to turn this xml content into
SQL> --
SQL> col contents for a50
SQL> select * from notes;
USERID TO_COL FROM_COL CONTENTS
---------- --------------- --------------- --------------------------------------------------
13300 Tove Jani bla bla bla bla bla
and
SQL> select * from user_to_numbers;
USERID NUMBER_RELATED
---------- --------------
13300 455
13300 456
13300 457
13300 458
13300 459
SQL>
SQL>
SQL>
Thanks in advance!
Andrey
[Updated on: Sat, 02 April 2016 09:28] Report message to a moderator |
|
|
|
|
|
| Re: XML structure into regular table [message #649663 is a reply to message #649662] |
Sat, 02 April 2016 10:25  |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
I've looked at the example pointed out, not sure I understand but I will post an example of an attempt to imitate the technique and will wait for your kind reply.
Regarding create statements:
create table notes
(
userID number(10,0),
to_col varchar2(15),
from_col varchar2(15),
contents varchar2(255)
);
create table user_to_numbers
(
userID number(10,0),
number_related number(10,0)
);
|
|
|
|
| Re: XML structure into regular table [message #649664 is a reply to message #649663] |
Sat, 02 April 2016 11:08 |
 |
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> insert all
2 when column_value = 1 then
3 into notes (userid, to_col, from_col, contents)
4 values (userid, to_col, from_col, contents)
5 when 1 = 1 then
6 into user_to_numbers (userid, number_related)
7 values (userid, number_related)
8 select userid, to_col, from_col, contents, column_value,
9 regexp_substr(numbers, '[^,]+', 1, column_value) number_related
10 from ( select userid, to_col, from_col, contents, numbers
11 from ( select '<note>
12 <userID>13300</userID>
13 <to_col>Tove</to_col>
14 <from_col>Jani</from_col>
15 <contents>bla bla bla bla bla</contents>
16 <i_numbers_related> 455,456,457,458,459</i_numbers_related>
17 </note>' val
18 from dual ) v,
19 xmltable('/note' passing xmltype(v.val)
20 columns
21 userid number(10,0) path '/note/userID',
22 to_col varchar2(15) path '/note/to_col',
23 from_col varchar2(15) path '/note/from_col',
24 contents varchar2(255) path '/note/contents',
25 numbers varchar2(255) path '/note/i_numbers_related') x ),
26 table(cast(multiset(select level from dual
27 connect by level <= regexp_count(numbers,',')+1)
28 as sys.odciNumberList))
29 /
6 rows created.
SQL> select * from notes;
USERID TO_COL FROM_COL CONTENTS
---------- --------------- --------------- ------------------------------
13300 Tove Jani bla bla bla bla bla
1 row selected.
SQL> select * from user_to_numbers;
USERID NUMBER_RELATED
---------- --------------
13300 455
13300 456
13300 457
13300 458
13300 459
5 rows selected. |
|
|
|
| Re: XML structure into regular table [message #649668 is a reply to message #649664] |
Sat, 02 April 2016 15:16 |
 |
Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is a slight variation. I am not sure which would be more efficient.
SCOTT@orcl> insert all
2 when rn = 1 then
3 into notes (userid, to_col, from_col, contents)
4 values (userid, to_col, from_col, contents)
5 when 1 = 1 then
6 into user_to_numbers (userid, number_related)
7 values (userid, number_related)
8 select x.userid, x.to_col, x.from_col, x.contents, t.number_related,
9 row_number () over (partition by x.userid order by rownum) rn
10 from (select '<note>
11 <userID>13300</userID>
12 <to_col>Tove</to_col>
13 <from_col>Jani</from_col>
14 <contents>bla bla bla bla bla</contents>
15 <i_numbers_related> 455,456,457,458,459</i_numbers_related>
16 </note>' val
17 from dual) v,
18 xmltable
19 ('/note'
20 passing xmltype(v.val)
21 columns
22 userid number(10,0) path '/note/userID',
23 to_col varchar2(15) path '/note/to_col',
24 from_col varchar2(15) path '/note/from_col',
25 contents varchar2(255) path '/note/contents',
26 numbers varchar2(255) path '/note/i_numbers_related') x,
27 xmltable
28 ('/numbers/num'
29 passing xmltype ('<numbers><num>' || replace (x.numbers, ',', '</num><num>') || '</num></numbers>')
30 columns
31 number_related varchar2(255) path '/num') t
32 /
6 rows created.
SCOTT@orcl> column contents format a30
SCOTT@orcl> select * from notes
2 /
USERID TO_COL FROM_COL CONTENTS
---------- --------------- --------------- ------------------------------
13300 Tove Jani bla bla bla bla bla
1 row selected.
SCOTT@orcl> select * from user_to_numbers
2 /
USERID NUMBER_RELATED
---------- --------------
13300 455
13300 456
13300 457
13300 458
13300 459
5 rows selected.
|
|
|
|
| Re: XML structure into regular table [message #649704 is a reply to message #649668] |
Mon, 04 April 2016 06:05 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Thank you both for sharing those cool techniques, which I find very interesting , as well as challenging to implement.
It helps me a lot when I have 1 small xml, but when my xml looks like a set of "notes", I couldn't understand how to "loop" around it, so I can have this done for every note...
This new xml structure would look like this:
<notes>
<note>
<userID>13300</userID>
<to_col>Tove</to_col>
<from_col>Jani</from_col>
<contents>bla bla bla bla bla</body>
<i_numbers_related> 455,456,457,458,459</i_numbers_related>
</note>
<note>
<userID>13400</userID>
<to_col>Dani</to_col>
<from_col>Jay</from_col>
<contents>Tam tam tam</body>
<i_numbers_related> 460,461,462</i_numbers_related>
</note>
</notes>
[Updated on: Mon, 04 April 2016 06:07] Report message to a moderator |
|
|
|
| Re: XML structure into regular table [message #649705 is a reply to message #649704] |
Mon, 04 April 2016 06:12 |
|
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
You just have to modify the input path in the first xmltable (line 28 below):
SQL> truncate table notes;
Table truncated.
SQL> truncate table user_to_numbers;
Table truncated.
SQL> insert all
2 when column_value = 1 then
3 into notes (userid, to_col, from_col, contents)
4 values (userid, to_col, from_col, contents)
5 when 1 = 1 then
6 into user_to_numbers (userid, number_related)
7 values (userid, number_related)
8 select userid, to_col, from_col, contents, column_value,
9 regexp_substr(numbers, '[^,]+', 1, column_value) number_related
10 from ( select userid, to_col, from_col, contents, numbers
11 from ( select '<notes>
12 <note>
13 <userID>13300</userID>
14 <to_col>Tove</to_col>
15 <from_col>Jani</from_col>
16 <contents>bla bla bla bla bla</contents>
17 <i_numbers_related> 455,456,457,458,459</i_numbers_related>
18 </note>
19 <note>
20 <userID>13400</userID>
21 <to_col>Dani</to_col>
22 <from_col>Jay</from_col>
23 <contents>Tam tam tam</contents>
24 <i_numbers_related> 460,461,462</i_numbers_related>
25 </note>
26 </notes>' val
27 from dual ) v,
28 xmltable('/notes/note' passing xmltype(v.val)
29 columns
30 userid number(10,0) path '/note/userID',
31 to_col varchar2(15) path '/note/to_col',
32 from_col varchar2(15) path '/note/from_col',
33 contents varchar2(255) path '/note/contents',
34 numbers varchar2(255) path '/note/i_numbers_related') x ),
35 table(cast(multiset(select level from dual
36 connect by level <= regexp_count(numbers,',')+1)
37 as sys.odciNumberList))
38 /
10 rows created.
SQL> col contents format a30
SQL> select * from notes;
USERID TO_COL FROM_COL CONTENTS
---------- --------------- --------------- ------------------------------
13300 Tove Jani bla bla bla bla bla
13400 Dani Jay Tam tam tam
2 rows selected.
SQL> select * from user_to_numbers;
USERID NUMBER_RELATED
---------- --------------
13300 455
13300 456
13300 457
13300 458
13300 459
13400 460
13400 461
13400 462
8 rows selected.
Note you have to first fix your input XML which is invalid (we silently did it).
[Updated on: Mon, 04 April 2016 06:13] Report message to a moderator |
|
|
|
| Re: XML structure into regular table [message #649719 is a reply to message #649705] |
Mon, 04 April 2016 14:32 |
|
Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Same here. I made the same change, also on line 28. I also corrected the xml by changing body to contents, assuming that this was a partial excerpt, where some was removed from the middle.
SCOTT@orcl> insert all
2 when rn = 1 then
3 into notes (userid, to_col, from_col, contents)
4 values (userid, to_col, from_col, contents)
5 when 1 = 1 then
6 into user_to_numbers (userid, number_related)
7 values (userid, number_related)
8 select x.userid, x.to_col, x.from_col, x.contents, t.number_related,
9 row_number () over (partition by x.userid order by rownum) rn
10 from (select '<notes>
11 <note>
12 <userID>13300</userID>
13 <to_col>Tove</to_col>
14 <from_col>Jani</from_col>
15 <contents>bla bla bla bla bla</contents>
16 <i_numbers_related> 455,456,457,458,459</i_numbers_related>
17 </note>
18 <note>
19 <userID>13400</userID>
20 <to_col>Dani</to_col>
21 <from_col>Jay</from_col>
22 <contents>Tam tam tam</contents>
23 <i_numbers_related> 460,461,462</i_numbers_related>
24 </note>
25 </notes>' val
26 from dual) v,
27 xmltable
28 ('/notes/note'
29 passing xmltype(v.val)
30 columns
31 userid number(10,0) path '/note/userID',
32 to_col varchar2(15) path '/note/to_col',
33 from_col varchar2(15) path '/note/from_col',
34 contents varchar2(255) path '/note/contents',
35 numbers varchar2(255) path '/note/i_numbers_related') x,
36 xmltable
37 ('/numbers/num'
38 passing xmltype ('<numbers><num>' || replace (x.numbers, ',', '</num><num>') || '</num></numbers>')
39 columns
40 number_related varchar2(255) path '/num') t
41 /
10 rows created.
SCOTT@orcl> column contents format a30
SCOTT@orcl> select * from notes
2 /
USERID TO_COL FROM_COL CONTENTS
---------- --------------- --------------- ------------------------------
13300 Tove Jani bla bla bla bla bla
13400 Dani Jay Tam tam tam
2 rows selected.
SCOTT@orcl> select * from user_to_numbers
2 /
USERID NUMBER_RELATED
---------- --------------
13300 455
13300 456
13300 457
13300 458
13300 459
13400 460
13400 461
13400 462
8 rows selected.
|
|
|
|
Goto Forum:
Current Time: Sun Jan 26 00:48:46 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
