Lo mejor seria que almacenaras el XML en un campo tipo Clob, y crearas un procedimiento almacenado que se encague de leer la información que esta guardada en dicho campo.
ejemplo:
- - - - - - - - - - - - - - - - File begins here - - - - - - - - - - - - - - - -
<ROWSET>
<ROW>
<DOCID> 91739.1 </DOCID>
<SUBJECT> MTS: ORA-29855, DRG-50704, ORA-12154: on create index using Intermedia </SUBJECT>
<TYPE> PROBLEM </TYPE>
<CONTENT_TYPE> TEXT/PLAIN </CONTENT_TYPE>
<STATUS> PUBLISHED </STATUS>
<CREATION_DATE> 14-DEC-1999 </CREATION_DATE>
<LAST_REVISION_DATE> 05-JUN-2000 </LAST_REVISION_DATE>
<LANGUAGE> USAENG </LANGUAGE>
</ROW>
</ROWSET>
- - - - - - - - - - - - - - - - File ends here - - - - - - - - - - - - - - - -
How to Read XML File Stored in Clob Column and Extract Data to Different Table
------------------------------------------------------------------------------
1. Create directory object mapped to physical directory which contain the XML file.
Note: Oracle user should have (at least) read permission for the directory and the XML file.
CREATE DIRECTORY XML_DIR AS 'c:\ash';
2. Create a table containing a CLOB and insert a row for the XML file.
XML_CLOB stores the xml document in a clob column,
CREATE TABLE xml_clob(
docname VARCHAR2(100) PRIMARY KEY,
doc CLOB,
insertdate DATE DEFAULT SYSDATE);
3. Create PL/SQL procedure to insert the example.xml file into the
database table xml_clob.
CREATE OR REPLACE PROCEDURE insertXML (dirname IN VARCHAR2,
filename IN VARCHAR2)
IS
xmlfile BFILE;
myclob CLOB;
BEGIN
INSERT INTO xml_clob (docname, doc)
VALUES (filename, empty_clob())
RETURNING doc into myclob;
-- get a handle to the xml file on the OS
xmlfile := Bfilename(dirname,filename);
-- open the file
DBMS_LOB.fileOpen(xmlfile);
-- copy the contents of the file into the empty clob
DBMS_LOB.loadFromFile(myclob, xmlfile, dbms_lob.getLength(xmlfile));
END insertXML;
4. Create a table into which the XML document data has to be loaded after reading it from
the XML_CLOB table. Note that the column names of the table should match the XML tags.
CREATE TABLE XML_DOC (
DOCID VARCHAR2(10),
SUBJECT VARCHAR2(100),
TYPE VARCHAR2(20),
CONTENT_TYPE VARCHAR2(20),
STATUS VARCHAR2(20),
CREATION_DATE VARCHAR2(15),
LAST_REVISION_DATE VARCHAR2(15),
LANGUAGE VARCHAR2(10)
);
5. Create procedure to read XML stored in clob column of the xml_clob table, and
load extracted data into XML_DOC table.
CREATE OR REPLACE PROCEDURE loadxml1 AS
fil clob;
buffer varchar2(1000);
len INTEGER;
insrow INTEGER;
BEGIN
SELECT doc INTO fil FROM xml_clob WHERE docname='example.xml';
len := DBMS_LOB.GETLENGTH(fil);
DBMS_OUTPUT.PUT_LINE('length '||len);
DBMS_LOB.READ(fil,len,1,buffer);
xmlgen.resetOptions;
insrow := xmlgen.insertXML('xml_doc',buffer);
DBMS_OUTPUT.PUT_LINE('length ins '||insrow);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('In Exception');
DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
end;
