Home » Developer & Programmer » JDeveloper, Java & XML » Read XML Files! (Oracle 11g R2)
Read XML Files! [message #513839] Wed, 29 June 2011 11:52 Go to next message
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 #513841 is a reply to message #513839] Wed, 29 June 2011 12:03 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the definition of your table? Is this a lone XML column or many column of different types?

Regards
Michel
Re: Read XML Files! [message #513845 is a reply to message #513841] Wed, 29 June 2011 12:09 Go to previous messageGo to next message
aucrun
Messages: 114
Registered: February 2011
Senior Member
I want to load the content of the XML file into an single table, just like:
companyname VARCHAR2(50) NOT NULL
address VARCHAR2(100) NOT NULL
phone VARCHAR2(20) NULL
fax VARCHAR2(20) NULL
manager VARCHAR2(50) NOT NULL

It is just an simple sample, but it is just what I need.
Re: Read XML Files! [message #513853 is a reply to message #513845] Wed, 29 June 2011 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
Barbara Boehmer
Messages: 7860
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>

Previous Topic: XML to Oracle Plsql Conversion
Next Topic: PLS-00201 errot was displayed when i call a pl/sql function from java program
Goto Forum:
  


Current Time: Thu Apr 17 19:48:54 CDT 2014

Total time taken to generate the page: 0.13307 seconds