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 Go to next message
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 #649662 is a reply to message #649661] Sat, 02 April 2016 09:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There are many examples in this forum.
Search for XMLTABLE or XMLSEQUENCE.
With CREATE TABLE statements I may show you.
You can also find a close (but more complex) example here.

Re: XML structure into regular table [message #649663 is a reply to message #649662] Sat, 02 April 2016 10:25 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
Barbara Boehmer
Messages: 9077
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.

Previous Topic: No modifier vs Protected
Next Topic: Extract XML value with no local name
Goto Forum:
  


Current Time: Thu Mar 28 12:58:40 CDT 2024