Import XML File Into Oracle

We can import XML files into an Oracle database schema. First, we use BFILE data type to store the XML file name. Then we use xmltype() function to convert the file content into oracle XMLTYPE.

create table test_docs (id number, text_doc bfile);
truncate table test_docs;
insert into test_docs values (1, BFILENAME('DM_DUMP', 'test.xml'));

SQL> select xmltype(text_doc, nls_charset_id('AL32UTF8')) xml_doc from test_docs where id=1;

We may also create a new table containing the xmltype data type column using CTAS (Create Table As Select) query as shown below.

create table test_xml 
as select id, xmltype(text_doc, nls_charset_id('AL32UTF8')) xml_doc 
from test_docs;