-- xmlinsert.sql

SET SERVEROUTPUT ON

DECLARE
  context DBMS_XMLSTORE.ctxType;
  rowcount NUMBER;
  xmlData CLOB :=      --  XML data set here
    '<ROWSET>
       <ROW num="1">
         <ID>1</ID>
         <FIRSTNAME>Ted</FIRSTNAME>
         <LASTNAME>Striker</LASTNAME>
       </ROW>
       <ROW>
         <ID>2</ID>
         <FIRSTNAME>Inigo</FIRSTNAME>
         <LASTNAME>Montoya</LASTNAME>
       </ROW>
     </ROWSET>';
BEGIN
  context := DBMS_XMLSTORE.NEWCONTEXT('S1.PERSON'); -- get context
  DBMS_XMLSTORE.CLEARUPDATECOLUMNList(context); -- clear update settings
  DBMS_XMLSTORE.SETUPDATECOLUMN(context,'ID');  -- set fields to be updated
  DBMS_XMLSTORE.SETUPDATECOLUMN(context,'FIRSTNAME');
  DBMS_XMLSTORE.SETUPDATECOLUMN(context,'LASTNAME');
  rowcount := DBMS_XMLSTORE.INSERTXML(context, xmlData); -- insert new records
  DBMS_XMLSTORE.CLOSECONTEXT(context);
  DBMS_OUTPUT.PUT_LINE(rowcount || ' new rows inserted in person table.');
END;
/
