Solved

Converting XML File to XMLTYPE

Posted on 2008-10-14
2
1,535 Views
Last Modified: 2013-12-18
We are trying to load a XML text file to a Oracle XMLTYPE. Any suggestions on this would be really helpful.

Thanks in advance.
0
Comment
Question by:pras_gupta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 27

Expert Comment

by:sujith80
ID: 22718594
You can use sqlloader to load your XMLs into your table.
The control file would look something like this.

---------------------------------------------
LOAD DATA
INFILE 'file_list.txt'
 APPEND INTO TABLE TBL1
 FIELDS TERMINATED BY ','
 (fname FILLER CHAR(10),
  val LOBFILE(fname) TERMINATED BY EOF)
---------------------------------------------

Where file_list.txt is a plain text file with the name of your xml documents.
For example the contents would look like:

one.xml
two.xml

Where one.xml and two.xml are the xml files to be loaded. and the column VAL is of XMLTYPE in your table.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 125 total points
ID: 22720434
or use bfiles
DECLARE
    v_bfile   BFILE := BFILENAME ('DTEMP', 'test.xml');
    v_clob    CLOB;
    v_xml     XMLTYPE;
BEGIN
    DBMS_LOB.createtemporary (v_clob, TRUE);
    DBMS_LOB.OPEN (v_bfile, DBMS_LOB.lob_readonly);
    DBMS_LOB.loadfromfile (v_clob, v_bfile, DBMS_LOB.lobmaxsize);
    v_xml := XMLTYPE (v_clob)
END;

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question