DBMS_XMLSTORE does the opposite of DBMS_XMLGEN: it takes XML in the canonical ROWSET and ROW form and inserts, updates, or deletes table rows from it. Elements map to columns by name, and key columns identify the rows to change.
Code for This Guide
The main examples are in the examples/pkg-xml folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
ctx := dbms_xmlstore.newcontext('TABLE');
n := dbms_xmlstore.insertxml(ctx, xml);
dbms_xmlstore.setkeycolumn(ctx, 'COLUMN'); -- for update and delete
dbms_xmlstore.setupdatecolumn(ctx, 'COLUMN'); -- columns to update
n := dbms_xmlstore.updatexml(ctx, xml);
n := dbms_xmlstore.deletexml(ctx, xml);
dbms_xmlstore.closecontext(ctx);Element names must match column names, in uppercase.
The examples use a small table:
Setup:
create table gate_plan (flight_no varchar2(6) primary key, gate varchar2(4),
remark varchar2(40));Insert and Update from XML
Example:
declare
ctx dbms_xmlstore.ctxtype;
v_n number;
begin
ctx := dbms_xmlstore.newcontext('GATE_PLAN');
v_n := dbms_xmlstore.insertxml(ctx,
'<ROWSET><ROW><FLIGHT_NO>NM150</FLIGHT_NO><GATE>A1</GATE></ROW>
<ROW><FLIGHT_NO>NM101</FLIGHT_NO><GATE>C3</GATE></ROW></ROWSET>');
dbms_output.put_line(v_n || ' inserted');
dbms_xmlstore.setkeycolumn(ctx, 'FLIGHT_NO'); -- update and delete by key
dbms_xmlstore.setupdatecolumn(ctx, 'GATE');
dbms_xmlstore.setupdatecolumn(ctx, 'REMARK');
v_n := dbms_xmlstore.updatexml(ctx,
'<ROWSET><ROW><FLIGHT_NO>NM150</FLIGHT_NO><GATE>B12</GATE>
<REMARK>gate change</REMARK></ROW></ROWSET>');
dbms_output.put_line(v_n || ' updated');
dbms_xmlstore.closecontext(ctx);
end;
/
select * from gate_plan order by flight_no;Output:
2 inserted 1 updated PL/SQL procedure successfully completed. FLIGHT_NO GATE REMARK ____________ _______ ______________ NM101 C3 NM150 B12 gate change
INSERTXML adds a row for each ROW element. For the update, FLIGHT_NO is the key and GATE and REMARK are the columns to change, so NM150 moves to gate B12 with a remark. The example drops the table afterward.
Delete from XML
Example:
insert into gate_plan (flight_no, gate) values ('NM150', 'B12');
insert into gate_plan (flight_no, gate) values ('NM101', 'C3');
declare
ctx dbms_xmlstore.ctxtype := dbms_xmlstore.newcontext('GATE_PLAN');
v_n number;
begin
dbms_xmlstore.setkeycolumn(ctx, 'FLIGHT_NO');
v_n := dbms_xmlstore.deletexml(ctx,
'<ROWSET><ROW><FLIGHT_NO>NM101</FLIGHT_NO></ROW></ROWSET>');
dbms_output.put_line(v_n || ' deleted');
dbms_xmlstore.closecontext(ctx);
end;
/
select flight_no, gate from gate_plan;Output:
1 row inserted. 1 row inserted. 1 deleted PL/SQL procedure successfully completed. FLIGHT_NO GATE ____________ _______ NM150 B12
With FLIGHT_NO as the key, DELETEXML removes the row for each ROW element, here NM101. The example drops the table afterward.
Things to Know
- Columns not in the XML get their default or NULL on insert; set update columns to limit which ones change.
- Transform other XML layouts into ROWSET and ROW form first, for example with XMLTYPE.TRANSFORM.
- The changes are normal DML, part of the current transaction.
Related Guides
Conclusion
DBMS_XMLSTORE inserts, updates, and deletes rows from canonical XML, matching elements to columns by name. Set key and update columns for changes, and close the context when done.
