PURPOSE
-------
The purpose of this document is to provide a very simple example (from start to
finish) on how to insert, update and delete rows into Oracle database using XML
parser for PL/SQL
SCOPE & APPLICATION
-------------------
This document is not intended to teach XML. The purpose of this document is
to provide a working example of how you use some of the features of the Oracle
XDK.
How to DML XML rows into the database using XSU?
1.-Create the table
Code:
1
2
3
4
| CREATE TABLE empleados (empid NUMBER PRIMARY KEY,
empname VARCHAR2(30),
empjob VARCHAR2(30),
empsal NUMBER); |
2.-Create a procedure to insert rows into this table using XSU.
-- This procedure is generic for all tables
-- You can insert using this procedure in any table
-- xmlDoc can be VARCHAR2 or CLOB
Code:
1
2
3
4
5
6
7
8
9
10
| 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;
/ |
3.-Execute the procedure to insert
Assuming thet we have this XML document...
<?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>
<ROW num="1">
<EMPID>30</EMPID>
<EMPNAME>Louis Bald</EMPNAME>
<EMPJOB>Technical Specialist</EMPJOB>
<EMPSAL>400</EMPSAL>
</ROW>
<ROW num="1">
<EMPID>40</EMPID>
<EMPNAME>Anthony Flowers</EMPNAME>
<EMPJOB>Technical Team Leader</EMPJOB>
<EMPSAL>500</EMPSAL>
</ROW>
<ROW num="1">
<EMPID>50</EMPID>
<EMPNAME>George Monk</EMPNAME>
<EMPJOB>Support Consultant</EMPJOB>
<EMPSAL>200</EMPSAL>
</ROW>
</ROWSET>
... we can execute the procedure like this:
Code:
1
2
3
4
5
6
| SQL> exec InsertXML('<?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><ROW
num="1"><EMPID>30</EMPID><EMPNAME>Louis Bald</EMPNAME><EMPJOB>Technical Specialist</EMPJOB><EMPSAL>400</EMPSAL></ROW><ROW num="1"><EMPID>40</EMPID><EMPNAME>Anthony
Flowers</EMPNAME><EMPJOB>Technical Team Leader</EMPJOB><EMPSAL>500</EMPSAL></ROW><ROW num="1"><EMPID>50</EMPID><EMPNAME>George Monk</EMPNAME><EMPJOB>Support Consultant</EMPJOB><EMPSAL>200</EMPSAL></ROW></ROWSET>','empleados');
5 rows inserted
PL/SQL procedure successfully completed. |
4.-Create a procedure to update rows into this table using XSU.
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
| -- Like in insert procedure, xmlDoc can be VARVCHAR2 or CLOB
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;
/ |
5.-Execute the procedure to update rows...
Assuming thet we have this XML document...
<?xml version="1.0"?>
<ROWSET>
<ROW num="1">
<EMPID>10</EMPID>
<EMPNAME>Perry Smith Johnson</EMPNAME>
</ROW>
<ROW num="1">
<EMPID>20</EMPID>
<EMPJOB>Principal Support Consultant I</EMPJOB>
<EMPSAL>700</EMPSAL>
</ROW>
<ROW num="1">
<EMPID>30</EMPID>
<EMPSAL>700</EMPSAL>
</ROW>
</ROWSET>
... we can execute the procedure like this:
Code:
1
2
3
4
| SQL> exec UpdateEmpleados('<?xml version="1.0"?><ROWSET><ROW num="1"><EMPID>10</EMPID><EMPNAME>Perry Smith Johnson</EMPNAME></ROW><ROW
num="1"><EMPID>20</EMPID><EMPJOB>Principal Support Consultant I</EMPJOB><EMPSAL>700</EMPSAL></ROW><ROW num="1"><EMPID>30</EMPID><EMPSAL>700</EMPSAL></ROW></ROWSET>');
3 rows updated
PL/SQL procedure successfully completed. |
6.-Create a procedure to delete rows into this table using XSU.
Code:
1
2
3
4
5
6
7
8
9
10
11
12
| -- Like in above examples xmlDoc can be VARCHAR2 or CLOB
create or replace procedure DeleteEmpleados(xmlDoc IN VARCHAR2) is
delCtx DBMS_XMLSave.ctxType;
rows number;
begin
delCtx := DBMS_XMLSave.newContext('empleados');
DBMS_XMLSave.setKeyColumn(delCtx,'EMPID');
rows := DBMS_XMLSave.deleteXML(delCtx,xmlDoc);
dbms_output.put_line(to_char(rows) || ' rows deleted');
DBMS_XMLSave.closeContext(delCtx);
end;
/ |
Assuming thet we have this XML document...
<?xml version="1.0"?>
<ROWSET>
<ROW num="1">
<EMPID>50</EMPID>
</ROW>
</ROWSET>
... we can execute the procedure like this:
Code:
1
2
3
| SQL> exec DeleteEmpleados('<?xml version="1.0"?><ROWSET><ROW num="1"><EMPID>50</EMPID></ROW></ROWSET>');
1 rows deleted
PL/SQL procedure successfully completed. |
