Back to normal view: https://oracle-base.com/articles/9i/load-xmltype-from-file

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;

  l_dest_offset   INTEGER := 1;
  l_src_offset    INTEGER := 1;
  l_bfile_csid    NUMBER  := 0;
  l_lang_context  INTEGER := 0;
  l_warning       INTEGER := 0;
BEGIN
  DBMS_LOB.createtemporary (l_clob, TRUE);
  
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  -- loadfromfile deprecated.
  -- DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
  DBMS_LOB.loadclobfromfile (
    dest_lob      => l_clob,
    src_bfile     => l_bfile,
    amount        => DBMS_LOB.lobmaxsize,
    dest_offset   => l_dest_offset,
    src_offset    => l_src_offset,
    bfile_csid    => l_bfile_csid ,
    lang_context  => l_lang_context,
    warning       => l_warning);
  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.

Back to normal view: https://oracle-base.com/articles/9i/load-xmltype-from-file