Load XML into Database

Following is the extract from Oracle SQL & PLSQL Forum (Courtesy Billy Verreynne)

You can store XML as CLOB or XMLType objects.

You can easily load XML files into the database as CLOBs using the DBMS_LOB PL/SQL package - it has a load-from-file procedural call that can be made. Very easy to use.


drop table xml_clob;
drop table my_objects;
-- create a table to store a large XML doc in raw format
create table xml_clob ( doc CLOB )
/
-- create such a raw XML document, using the ALL_OBJECTS -- table
DECLARE
CURSOR curxml IS
SELECT xmlelement("ORA_OBJECT", xmlforest(object_id AS "ID", owner AS "OWNER", object_type AS "TYPE", object_name AS "NAME")) AS "XML_DATA"
FROM all_objects
WHERE rownum < 100001;

xml XMLTYPE;
c CLOB;
BEGIN
dbms_lob.createtemporary(c, TRUE);

OPEN curxml;

LOOP
FETCH curxml
INTO xml;

EXIT WHEN curxml%NOTFOUND;
DBMS_LOB.Append( c, xml.GetCLOBVal() );
-- dbms_lob.writeappend(c, LENGTH(xml.getclobval()), xml.getclobval());
END LOOP;

CLOSE curxml;

INSERT INTO xml_clob
VALUES ('' || c || ''
);

COMMIT;
END;

/
CREATE TABLE my_objects (obj_id NUMBER, obj_owner VARCHAR2(30), obj_type VARCHAR2(19), obj_name VARCHAR2(30) )

/
-- call proc with a raw XML clob and have it insert the contents
-- into a relational table
CREATE OR REPLACE PROCEDURE insertobjects(
xmldoc IN OUT NOCOPY CLOB
) IS
BEGIN
INSERT INTO my_objects
SELECT extractvalue(VALUE(xml), 'ORA_OBJECT/ID')
, extractvalue(VALUE(xml), 'ORA_OBJECT/OWNER')
, extractvalue(VALUE(xml), 'ORA_OBJECT/TYPE')
, extractvalue(VALUE(xml), 'ORA_OBJECT/NAME')
FROM TABLE(SELECT xmlsequence(EXTRACT(XMLTYPE(xmldoc), 'DATASET/*'))
FROM dual) xml;

COMMIT;
END;
/
-- the test (processing 10,000 XML rows):
set timing on
declare c CLOB;
BEGIN
SELECT doc
INTO c
FROM xml_clob;

insertobjects(c);
END;
/

set timing offselect count(*) from my_objects;
select * from my_objects where rownum < 4;
--eof