OraFAQ Forum: Server Utilities » Loading from xml file to staging table in Oracle

Home » RDBMS Server » Server Utilities » Loading from xml file to staging table in Oracle

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Loading from xml file to staging table in Oracle [message #418014]

Wed, 12 August 2009 09:07

araval001
Messages: 2
Registered: August 2009

Junior Member

Hello Everyone ..

I wanted to loaded data from xml file to staging table using sql loader.

Here's the data look in xml file :

<?xml version="1.0"?>
<Customers>
<Customer status="update">
<CustID>1</CustID>
<Company>Bell South</Company>
<City>New York</City>
<Address>
<add1>a1</add1>
<add2>a2</add2>
<add3>a3</add3>
</Address>
</Customer>
<Customer status="update">
<CustID>2</CustID>
<Company>Barnes & Noble</Company>
<City>New York</City>
<Address>
<add1>a1</add1>
<add2>a2</add2>
<add3>a3</add3>
</Address>
</Customer>
<Customer status="update">
<CustID>3</CustID>
<Company>Comp USA</Company>
<City>Tampa</City>
<Address>
<add1>a1</add1>
<add2>a2</add2>
<add3>a3</add3>
</Address>
</Customer>
<Customer status="update">
<CustID>4</CustID>
<Company>Borders</Company>
<City>Charlotte</City>
<Address>
<add1>a1</add1>
<add2>a2</add2>
<add3>a3</add3>
</Address>
</Customer>
</Customers>
========================
Here's the control file or (ctl file)

load data
infile 'customer.xml' "str '</Customer>'"
truncate

into table customers
(
dummy filler terminated by "<Customer>",
custid enclosed by "<CustID>" and "</CustID>",
company enclosed by "<Company>" and "</Company>",
city enclosed by "<City>" and "</City>"
)

===========================
Problem is i dont know how to read value for "status" at customer tag and address values (add1, add2,add3).

Any help would be appreciated .

Report message to a moderator

Re: Loading from xml file to staging table in Oracle [message #418018 is a reply to message #418014]

Wed, 12 August 2009 09:38

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

please post the table ddl, Oracle version and how you want the those attribute values to be handled in database table.
Is status a separate column in customers table? If not where would the values go?
Should add1,add2,add3 concatenated into address?

[Updated on: Wed, 12 August 2009 09:45]

Report message to a moderator

Re: Loading from xml file to staging table in Oracle [message #418024 is a reply to message #418014]

Wed, 12 August 2009 10:00

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Don't use SQL*Loader for this, use external table and native Oracle XML function or package.

Regards
Michel

Report message to a moderator

Re: Loading from xml file to staging table in Oracle [message #418131 is a reply to message #418014]

Thu, 13 August 2009 02:05

araval001
Messages: 2
Registered: August 2009

Junior Member

Thank u .. for prompt reply

ORACLE VER : 10g

ddl wud be as such :

CREATE TABLE CUSTOMER
(
CustID CHAR(20),
Company VARCHAR2(25) ,
City VARCHAR2(255) ,
status VARCHAR2(25),
Address VARCHAR2(1000) );

Address column all three values of add1, add2 ,add3 (seperated by "|").

Report message to a moderator

Re: Loading from xml file to staging table in Oracle [message #418153 is a reply to message #418131]

Thu, 13 August 2009 03:44

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> col custid format a6
SQL> col company format a20
SQL> col city format a10
SQL> col status format a6
SQL> col address format a30
SQL> set define off
SQL> with
  2    data as (
  3      select xmltype(
  4  '<?xml version="1.0"?>
  5  <Customers>
  6  <Customer status="update"> 
  7  <CustID>1</CustID>
  8  <Company>Bell South</Company> 
  9  <City>New York</City>
 10  <Address>
 11  <add1>a1</add1> 
 12  <add2>a2</add2> 
 13  <add3>a3</add3> 
 14  </Address>
 15  </Customer>
 16  <Customer status="update"> 
 17  <CustID>2</CustID>
 18  <Company>Barnes &amp; Noble</Company> 
 19  <City>New York</City>
 20  <Address>
 21  <add1>a1</add1> 
 22  <add2>a2</add2> 
 23  <add3>a3</add3> 
 24  </Address>
 25  </Customer>
 26  <Customer status="update"> 
 27  <CustID>3</CustID>
 28  <Company>Comp USA</Company> 
 29  <City>Tampa</City>
 30  <Address>
 31  <add1>a1</add1> 
 32  <add2>a2</add2> 
 33  <add3>a3</add3> 
 34  </Address>
 35  </Customer> 
 36  <Customer status="update"> 
 37  <CustID>4</CustID>
 38  <Company>Borders</Company> 
 39  <City>Charlotte</City>
 40  <Address>
 41  <add1>a1</add1> 
 42  <add2>a2</add2> 
 43  <add3>a3</add3> 
 44  </Address>
 45  </Customer>
 46  </Customers>') val
 47      from dual
 48    )
 49  select extractvalue(value(x),'/Customer/CustID') custid,
 50         extractvalue(value(x),'/Customer/Company') company,
 51         extractvalue(value(x),'/Customer/City') city,
 52         extractvalue(value(x),'/Customer/@status') status,
 53         extractvalue(value(x),'/Customer/Address/add1') || ' | ' ||
 54         extractvalue(value(x),'/Customer/Address/add2') || ' | ' ||
 55         extractvalue(value(x),'/Customer/Address/add3') address
 56  from data,
 57       table(xmlsequence(extract(data.val, '/Customers/Customer'))) x
 58  /
CUSTID COMPANY              CITY       STATUS ADDRESS
------ -------------------- ---------- ------ ------------------------------
1      Bell South           New York   update a1 | a2 | a3
2      Barnes & Noble       New York   update a1 | a2 | a3
3      Comp USA             Tampa      update a1 | a2 | a3
4      Borders              Charlotte  update a1 | a2 | a3

4 rows selected.

Regards
Michel

Report message to a moderator

Re: Loading from xml file to staging table in Oracle [message #418279 is a reply to message #418153]

Thu, 13 August 2009 16:24

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

The following demonstrates how to use SQL*Loader to load the data from the customer.xml file provided into a val column of xmltype in a staging table, then uses the select statement by Michel Cadot for insertion into the customer table, substituting the staging table for the sub-query factoring (with) clause.

-- customer.ctl:

LOAD DATA
INFILE *
TRUNCATE INTO TABLE staging
FIELDS TERMINATED BY ','
  (ext_fname FILLER CHAR (61),
   val       LOBFILE(ext_fname) TERMINATED BY EOF)
BEGINDATA:
customer.xml,

SCOTT@orcl_11g> CREATE TABLE staging
  2    (val  XMLTYPE)
  3  /

Table created.

SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=customer.ctl LOG=customer.log

SCOTT@orcl_11g> SELECT * FROM staging
  2  /

VAL
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<Customers>
<Customer status="update">
<CustID>1</CustID>
<Company>Bell South</Company>
<City>New York</City>
<Address>
<add1>a1</add1>
<add2>a2</add2>
<add3>a3</add3>
</Address>
</Customer>
<Customer status="update">
<CustID>2</CustID>
<Company>Barnes &amp; Noble</Company>
<City>New York</City>
<Address>
<add1>a1</add1>
<add2>a2</add2>
<add3>a3</add3>
</Address>
</Customer>
<Customer status="update">
<CustID>3</CustID>
<Company>Comp USA</Company>
<City>Tampa</City>
<Address>
<add1>a1</add1>
<add2>a2</add2>
<add3>a3</add3>
</Address>
</Customer>
<Customer status="update">
<CustID>4</CustID>
<Company>Borders</Company>
<City>Charlotte</City>
<Address>
<add1>a1</add1>
<add2>a2</add2>
<add3>a3</add3>
</Address>
</Customer>
</Customers>


1 row selected.

SCOTT@orcl_11g> CREATE TABLE CUSTOMER
  2  (
  3  CustID CHAR(20),
  4  Company VARCHAR2(25) ,
  5  City VARCHAR2(255) ,
  6  status VARCHAR2(25),
  7  Address VARCHAR2(1000) )
  8  /

Table created.

SCOTT@orcl_11g> -- insert using code my Michel Cadot,
SCOTT@orcl_11g> -- substituting staging table for subquery factoring:
SCOTT@orcl_11g> col custid  format a6
SCOTT@orcl_11g> col company format a20
SCOTT@orcl_11g> col city    format a10
SCOTT@orcl_11g> col status  format a6
SCOTT@orcl_11g> col address format a30
SCOTT@orcl_11g> set define  off
SCOTT@orcl_11g> insert into customer
  2  select extractvalue(value(x),'/Customer/CustID') custid,
  3  	    extractvalue(value(x),'/Customer/Company') company,
  4  	    extractvalue(value(x),'/Customer/City') city,
  5  	    extractvalue(value(x),'/Customer/@status') status,
  6  	    extractvalue(value(x),'/Customer/Address/add1') || ' | ' ||
  7  	    extractvalue(value(x),'/Customer/Address/add2') || ' | ' ||
  8  	    extractvalue(value(x),'/Customer/Address/add3') address
  9  from   staging,
 10  	    table(xmlsequence(extract(staging.val, '/Customers/Customer'))) x
 11  /

4 rows created.

SCOTT@orcl_11g> select * from customer
  2  /

CUSTID COMPANY              CITY       STATUS ADDRESS
------ -------------------- ---------- ------ ------------------------------
1      Bell South           New York   update a1 | a2 | a3
2      Barnes & Noble       New York   update a1 | a2 | a3
3      Comp USA             Tampa      update a1 | a2 | a3
4      Borders              Charlotte  update a1 | a2 | a3

4 rows selected.

SCOTT@orcl_11g>

Report message to a moderator

Re: Loading from xml file to staging table in Oracle [message #418281 is a reply to message #418279]

Thu, 13 August 2009 16:56

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

If the customer.xml file is on your client, not your server, then SQL*Loader is the only way to go. However, if your customer.xml file is on your server or you can move it to your server, then you can insert directly from that file, as demonstrated below.

SCOTT@orcl_11g> CREATE TABLE CUSTOMER
  2  (
  3  CustID CHAR(20),
  4  Company VARCHAR2(25) ,
  5  City VARCHAR2(255) ,
  6  status VARCHAR2(25),
  7  Address VARCHAR2(1000) )
  8  /

Table created.

SCOTT@orcl_11g> create or replace directory my_dir as 'c:\oracle11g'
  2  /

Directory created.

SCOTT@orcl_11g> set define  off
SCOTT@orcl_11g> insert into customer
  2  select extractvalue(column_value,'/Customer/CustID') custid,
  3  	    extractvalue(column_value,'/Customer/Company') company,
  4  	    extractvalue(column_value,'/Customer/City') city,
  5  	    extractvalue(column_value,'/Customer/@status') status,
  6  	    extractvalue(column_value,'/Customer/Address/add1') || ' | ' ||
  7  	    extractvalue(column_value,'/Customer/Address/add2') || ' | ' ||
  8  	    extractvalue(column_value,'/Customer/Address/add3') address
  9  from   TABLE
 10  	      (XMLSEQUENCE
 11  		 (EXTRACT
 12  		    (XMLTYPE
 13  		       (BFILENAME ('MY_DIR', 'customer.xml'),
 14  			NLS_CHARSET_ID ('WE8MSWIN1252')),
 15  		     '/Customers/Customer')))
 16  /

4 rows created.

SCOTT@orcl_11g> col custid  format a6
SCOTT@orcl_11g> col company format a20
SCOTT@orcl_11g> col city    format a10
SCOTT@orcl_11g> col status  format a6
SCOTT@orcl_11g> col address format a30
SCOTT@orcl_11g> select * from customer
  2  /

CUSTID COMPANY              CITY       STATUS ADDRESS
------ -------------------- ---------- ------ ------------------------------
1      Bell South           New York   update a1 | a2 | a3
2      Barnes & Noble       New York   update a1 | a2 | a3
3      Comp USA             Tampa      update a1 | a2 | a3
4      Borders              Charlotte  update a1 | a2 | a3

4 rows selected.

SCOTT@orcl_11g>

[Updated on: Thu, 13 August 2009 17:00]

Report message to a moderator

Previous Topic:	Collective Export and Import
Next Topic:	Loading data into database using SQL Loader

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 19:26:59 CST 2024