8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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...