Load XMLTYPE From File

Related articles.

In a recent forum thread I was asked how I would load an XMLTYPE column in a table from a file. This article explain one way of doing this.

First we create the necessary schema objects.

CREATE OR REPLACE DIRECTORY xml_dir AS 'c:temp';

CREATE TABLE xml_tab (
  id        NUMBER(10),
  filename  VARCHAR2(100),
  xml       XMLTYPE
)
/

ALTER TABLE xml_tab ADD (
  CONSTRAINT xml_tab_pk PRIMARY KEY (id)
)
/

CREATE SEQUENCE xml_tab_seq;

Next we create a procedure to load a file into the table.

CREATE OR REPLACE PROCEDURE load_xml (p_dir       IN  VARCHAR2,
                                      p_filename  IN  VARCHAR2) AS
  l_bfile  BFILE := BFILENAME(p_dir, p_filename);
  l_clob   CLOB;
BEGIN
  DBMS_LOB.createtemporary (l_clob, TRUE);
  
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
  DBMS_LOB.fileclose(l_bfile);

  INSERT INTO xml_tab (
    id,
    filename,
    xml
  )
  VALUES (
    xml_tab_seq.NEXTVAL,
    p_filename,
    XMLTYPE.createXML(l_clob)
  );
  COMMIT;
  
  DBMS_LOB.freetemporary (l_clob);
END;
/

Assuming the emp.xml is present in the appropriate location it can be loaded using the following command.

EXEC load_xml(p_dir => 'XML_DIR', p_filename => 'emp.xml');
The contents of the XMLTYPE can be checked using the following query.
SET LONG 5000
SELECT xml
FROM   xml_tab;

For more information see:

Hope this helps. Regards Tim...

Back to the Top.