| Read XML Files! [message #513839] |
Wed, 29 June 2011 11:52  |
 |
aucrun
Messages: 114
Registered: February 2011
|
Senior Member |
|
|
Hi everyone,
I need to load (using SQL Loader) an huge XML file, with several hundreds of records into an Oracle Table.
The XML file schema is pretty simple, and it's anything like this:
<dataroot>
<record>
<companyname>LimitSoft S.A.</companyname>
<address>Street Number 1</address>
<phone>322343242</phone>
<fax>3234424</fax>
<manager>Paul Wilkinson</manager>
</record>
<dataroot>
<record>
<companyname>Pointless Inc.</companyname>
<address>Street of Pointless Inc.</address>
<fax>23424424</fax>
<manager>Marc Anthon</manager>
</record>
</dataroot>
I'm trying to use the help included in this link (http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28369/xdb25loa.htm#BGBDDEDD) but I'm not understaning it.
When they refer to schema 'http://www.oracle.com/person.xsd', what should I use?? I do not need to use the Oracle website to register anything, right?
Thanks!
[Updated on: Wed, 29 June 2011 11:55] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Read XML Files! [message #513853 is a reply to message #513845] |
Wed, 29 June 2011 12:49  |
 |
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Create a file containing the list of files to upload:
C:\>type liste.txt
xml.txt
This file, I called "xml.txt", contains your data (with a quick fix as your data were not a correct xml):
C>type xml.txt
<dataroot>
<record>
<companyname>LimitSoft S.A.</companyname>
<address>Street Number 1</address>
<phone>322343242</phone>
<fax>3234424</fax>
<manager>Paul Wilkinson</manager>
</record>
<record>
<companyname>Pointless Inc.</companyname>
<address>Street of Pointless Inc.</address>
<fax>23424424</fax>
<manager>Marc Anthon</manager>
</record>
</dataroot>
Now I create an external table to bind your file(s):
SQL> create table ext (f varchar2(250), x clob)
2 organization external (
3 type oracle_loader
4 default directory mydir
5 access parameters (
6 records delimited by newline
7 nobadfile
8 nologfile
9 nodiscardfile
10 fields terminated by ','
11 (f char)
12 column transforms (x from lobfile (f) from (mydir) clob)
13 )
14 location ('liste.txt')
15 )
16 reject limit unlimited
17 /
Table created.
SQL> select * from ext;
F
------------------------------------------------------------------------
X
------------------------------------------------------------------------
xml.txt
<dataroot>
<record>
<companyname>LimitSoft S.A.</companyname>
<address>Street Number 1</address>
<phone>322343242</phone>
<fax>3234424</fax>
<manager>Paul Wilkinson</manager>
</record>
<record>
<companyname>Pointless Inc.</companyname>
<address>Street of Pointless Inc.</address>
<fax>23424424</fax>
<manager>Marc Anthon</manager>
</record>
</dataroot>
1 row selected.
1 row as there is one file.
Now I can query this external table as I want to match your final table (I modified the length to post each row in a single line):
SQL> select x.companyname, x.address, x.phone, x.fax, x.manager
2 from ext t,
3 xmltable ('/dataroot/record'
4 passing xmltype (t.x)
5 columns
6 "COMPANYNAME" varchar2(11) path '/record/companyname',
7 "ADDRESS" varchar2(20) path '/record/address',
8 "PHONE" varchar2(10) path '/record/phone',
9 "FAX" varchar2(10) path '/record/fax',
10 "MANAGER" varchar2(20) path '/record/manager'
11 ) x
12 /
COMPANYNAME ADDRESS PHONE FAX MANAGER
----------- -------------------- ---------- ---------- --------------------
LimitSoft S Street Number 1 322343242 3234424 Paul Wilkinson
Pointless I Street of Pointless 23424424 Marc Anthon
2 rows selected.
With just an INSERT INTO SELECT ... you can load your table.
Regards
Michel
[Updated on: Wed, 29 June 2011 12:53] Report message to a moderator |
|
|
|
| Re: Read XML Files! [message #513862 is a reply to message #513853] |
Wed, 29 June 2011 13:46 |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can do it in fewer steps without SQL*Loader or an external table, as long as the file is on your server, as demonstrated below.
-- contents of file c:\my_oracle_files\xml.txt
<dataroot>
<record>
<companyname>LimitSoft S.A.</companyname>
<address>Street Number 1</address>
<phone>322343242</phone>
<fax>3234424</fax>
<manager>Paul Wilkinson</manager>
</record>
<record>
<companyname>Pointless Inc.</companyname>
<address>Street of Pointless Inc.</address>
<fax>23424424</fax>
<manager>Marc Anthon</manager>
</record>
</dataroot>
-- table to load data into:
SCOTT@orcl_11gR2> create table oracle_table
2 (companyname VARCHAR2 (14) NOT NULL,
3 address VARCHAR2 (24) NOT NULL,
4 phone VARCHAR2 (10),
5 fax VARCHAR2 (10),
6 manager VARCHAR2 (14) NOT NULL)
7 /
Table created.
-- oracle directory object:
SCOTT@orcl_11gR2> create or replace directory my_dir as 'c:\my_oracle_files'
2 /
Directory created.
-- insert:
SCOTT@orcl_11gR2> insert into oracle_table
2 (companyname, address, phone, fax, manager)
3 select x.companyname, x.address, x.phone, x.fax, x.manager
4 from (select xmltype (bfilename ('MY_DIR', 'xml.txt'), NLS_CHARSET_ID ('WE8MSWIN1252')) x
5 from dual) t,
6 xmltable
7 ('/dataroot/record'
8 passing t.x
9 columns
10 "COMPANYNAME" varchar2(11) path '/record/companyname',
11 "ADDRESS" varchar2(20) path '/record/address',
12 "PHONE" varchar2(10) path '/record/phone',
13 "FAX" varchar2(10) path '/record/fax',
14 "MANAGER" varchar2(20) path '/record/manager') x
15 /
2 rows created.
-- results:
SCOTT@orcl_11gR2> select * from oracle_table
2 /
COMPANYNAME ADDRESS PHONE FAX MANAGER
-------------- ------------------------ ---------- ---------- --------------
LimitSoft S Street Number 1 322343242 3234424 Paul Wilkinson
Pointless I Street of Pointless 23424424 Marc Anthon
2 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
