• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1570
  • Last Modified:

Converting XML File to XMLTYPE

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
pras_gupta
Asked:
pras_gupta
1 Solution
 
sujith80Commented:
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
 
sdstuberCommented:
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now