Solved

Converting XML File to XMLTYPE

Posted on 2008-10-14
2
1,508 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
2 Comments
 
LVL 27

Expert Comment

by:sujith80
Comment Utility
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 73

Accepted Solution

by:
sdstuber earned 125 total points
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.
This video shows how to recover a database from a user managed backup

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now