Using XML to pass data to an Oracle Stored Procedure

[CF_DAWG]

Hello all!

In my environment, we use CFMX 7, and Oracle 9i. Currently, when I need to
update or insert multiple records at the same time, I loop over the stored
procedure in my CF code and wrap all that in a cftransaction tag, so if
something goes wrong, it will rollback.

But, I don't think this is the best way to do this, and would rather call a
stored procedure once and pass all the data to the SP in XML in a CLOB field,
parse it in the SP and loop over the insert statement in the SP. This will mean
only one hit to the database and improve performance, along with allowing the
database to control rollbacks and processing.

Has anyone done something like this? Any direction or help would be
appreciated!

Thanks!

[Frickie]

In general, you may want to do something like this:

In Oracle, create a table, say, by the name xmldocs, with, say, docid as the
PK and a CLOB column, say, xmlcontent, to hold the XML string.

In Oracle, create a stored procedure that accepts an XML docid as an input
parameter. The code of the procedure should then select the XML from the table
xmldocs (by docid), parse it and do whatever you want with it.

In your CFM page, insert your XML into the oracle table xmldocs returning the
newly created docid.

In your CFM page, using the new docid, call your Oracle stored procedure to
consume the just inserted XML. Make sure you pass the docid to your Oracle's
procedure.

Optionally, delete the XML doc from your xmldocs table.

[tconley]

There is also the XMLTYPE data type within Oracle.

With the XMLTYPE data type you can then use xpath expressions within your sql
statements, which should eliminate the issue of having to parse the xml.

[CF_DAWG]

Thanks for all the help guys! Frickie, do you have any code samples you would
be willing to share with me? tconley, do you know how to use the xmltype via CF
calling an Oracle stored procedure. I looked, and there does not seem to be one
like the cf_sql_varchar for example.

Thanks again!

[CF_DAWG]

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!