Summary Inserting XML file into a table using DBMS_XMLSave Package

I'm using Oracle 8.1.7 and the XDK 9.0.1.1.0A for PL/SQL

You can get the latest version of the XDK at OTN

http://otn.oracle.com/software/tech/xml/xdk_plsql/content.html

Well I've been making some snippets for the opposite of this one, with the DBMS_XMLQuery you get a SQL->XML functionality, and the DBMS_XMLSave will provide the XML->SQL functionality, it's important that you are able to insert the xml files into a table. This package has a lot's of possibilities for customization of the input xml file, including the use of a XSLT stylesheet to transform your xml file.

But in the end the xml file must be in the form of:
<ROWSET>
  <ROW>
     <FIELDNAME></FIELDNAME>
  </ROW>
</ROWSET>

Like the DBMS_XMLQuery the ROWSET and the ROW elements can be customized through the use of the procedures setrowsettag and setrowtag 

Carefull that the XSU utility is case sensitive so <ROW> is not the same thing as <row>, you can override this by using the setignorecase procedure. Also to mention that the tagnames should match the field names, if you have a tagname that doesn't have a correspondent field, the tag will be ignored, if it exist a field that doesn't have a corresponding tag, the field value will be null or the default value.

So If you have a XML file that is formatted this way, you can easily insert it into a oracle table using the package DBMS_XMLSave.

You have to create the directory were the files are located, you can use this to:

CREATE OR REPLACE DIRECTORY FILESDIR AS 'D:\ OraTest\'

I've made a separated function that loads the external file into a clob and return the clob.

FUNCTION LoadClobFromFile (p_FileName IN VARCHAR2)

RETURN CLOB IS

srcFile BFILE := BFILENAME('FILESDIR',p_FileName);

intLen INT;

tmpClob CLOB := EMPTY_CLOB;

BEGIN

DBMS_LOB.CREATETEMPORARY(tmpClob,TRUE,DBMS_LOB.SESSION);

DBMS_LOB.FILEOPEN(srcFile,DBMS_LOB.LOB_READONLY);

DBMS_LOB.OPEN(tmpClob,DBMS_LOB.LOB_READWRITE);

intLen := DBMS_LOB.GETLENGTH(srcFile);

DBMS_LOB.LOADFROMFILE(tmpClob,srcFile,intLen);

RETURN tmpClob;

DBMS_LOB.FILECLOSEALL();

EXCEPTION

WHEN OTHERS THEN

BEGIN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

DBMS_LOB.FILECLOSEALL();

COMMIT;

END;

END;

And the actual procedure is very simple:

PROCEDURE InsertXML is

queryCtx DBMS_XMLsave.ctxType;

result CLOB;

rowsInserted INT;

BEGIN

--Defines the query

queryCtx := DBMS_XMLSave.newContext('customer');

result := LoadClobFromFile('OraCustIn.xml');

rowsInserted := DBMS_XMLSave.insertXML(queryCtx,result);

DBMS_XMLSave.closeContext(queryCtx);

END;

Pedro Gil

http://www.vbxml.com/xselerator/default.asp the best XSLT IDE.