OraFAQ Forum: Server Utilities » load xml file/data in oracle table through sql*loader

Home » RDBMS Server » Server Utilities » load xml file/data in oracle table through sql*loader (oracle 9i R2 on sun solaris)

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

load xml file/data in oracle table through sql*loader [message #279466]

Thu, 08 November 2007 11:17

poratips
Messages: 345
Registered: April 2005
Location: IL

Senior Member

Hi,
I am currently laoding data through sql*loader d incoming file is regular text file, but now they will be supplying xml file and i wanted to load xml data file in this table.
How can I handle this?
Any type of guidence will be appreciated.
Do I need to create table using xmltype column also?

My current table is:

[B]CREATE TABLE SAL_SMP_ORD
(
C_DIV_ID VARCHAR2(40 BYTE) NOT NULL,
C_ID VARCHAR2(20 BYTE) NOT NULL,
DIV_ID VARCHAR2(20 BYTE) NOT NULL,
ORD_TOTAL NUMBER NOT NULL,
MON_ORD_TOTAL NUMBER NOT NULL,
LAST_UPDATED VARCHAR2(16 BYTE)
)[/B]
[U]My current Control file[/U]
[B]LOAD DATA
INFILE 'smpTot'
TRUNCATE
INTO TABLE SAL_SMP_ORD
TRAILING NULLCOLS
(C_DIV_ID Position(01:13),
C_ID Position(14:15),
DIV_ID Position(16:26),
ORD_TOTAL Position(27:40),
MON_ORD_TOTAL Position(41:54),
LAST_UPDATED Position(55:70)
)[/B]
thanks,

[Updated on: Mon, 03 December 2007 06:09] by Moderator

Report message to a moderator

Re: load xml file/data in oracle table through sql*loader [message #279468 is a reply to message #279466]

Thu, 08 November 2007 11:32

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

Check this

http://asktom.oracle.com/pls/asktom/f?p=100:1:4303111601776077::NO:RP::

Report message to a moderator

Re: load xml file/data in oracle table through sql*loader [message #279528 is a reply to message #279468]

Thu, 08 November 2007 21:40

poratips
Messages: 345
Registered: April 2005
Location: IL

Senior Member

Thanks for your response.
I tried to look into but couldn't find much or might be understand as lots of different example.
Can anyone please let me know how can I use in sql*loader?
Do I need to add any column like xmldatatype?
I will really appreciated your help.

Thanks,

Report message to a moderator