Insert into oracle table By XML In StoreProcedure

Well, after a bit of looking on Oracle's Metalink, I found some information on
passing XML to stored procedures via varchars or CLOBs. Here are examples of an
insert and of an update:

Create procedure to take in xml as a varchar or a clob:

create or replace procedure InsertXML(xmlDoc IN VARCHAR2, tableName IN
VARCHAR2) is

insCtx DBMS_XMLSave.ctxType;

rows number;

begin

insCtx := DBMS_XMLSave.newContext(tableName); -- get the context handle

rows := DBMS_XMLSave.insertXML(insCtx,xmlDoc); -- this inserts the document

dbms_output.put_line(to_char(rows) || ' rows inserted');

DBMS_XMLSave.closeContext(insCtx); -- this closes the handle

end;

The xml text that is passed must be in the form of:

<?xml version="1.0"?>

<ROWSET>

<ROW num="1">

<EMPID>10</EMPID>

<EMPNAME>Perry Smith</EMPNAME>

<EMPJOB>Manager</EMPJOB>

<EMPSAL>800</EMPSAL>

</ROW>

<ROW num="1">

<EMPID>20</EMPID>

<EMPNAME>John Calvach</EMPNAME>

<EMPJOB>Principal Support Consultant</EMPJOB>

<EMPSAL>900</EMPSAL>

</ROW>
</ROWSET>

Notice that the tags nested in the <ROW> tag are the same as the columns in
any table you would use. I don't know if it is case sensitive or not, but I
build all the xml as uppercase just in case.

This is using the internal Oracle XML parser for PL/SQL.

The update procedure is a bit different, because you have to tell it which
item is the primary key This procedure would update the empleados table which
has a primary key of empid:

create or replace procedure UpdateEmpleados ( xmlDoc IN VARCHAR2) is

updCtx DBMS_XMLSave.ctxType;

rows number;

begin

updCtx := DBMS_XMLSave.newContext('empleados'); -- get the context

DBMS_XMLSave.clearUpdateColumnList(updCtx); -- clear the update settings..

DBMS_XMLSave.setKeyColumn(updCtx,'EMPID'); -- set EMPNO as key column

rows := DBMS_XMLSave.updateXML(updCtx,xmlDoc); -- update the table.

dbms_output.put_line(to_char(rows) || ' rows updated');

DBMS_XMLSave.closeContext(updCtx); -- close the context..!

end;

And that's it! It seems to work quite well, and now I don't have to use the
<cftransaction> tag and let the database handle all rollbacks, etc.

Thanks for all the help on this!