-- selfinsert.sql

DECLARE
  context DBMS_XMLSTORE.ctxType;
  rowcount NUMBER;
  xmlData CLOB :=      --  XML data set here
    '<ROWSET>
       <ROW num="1">
         <ID>100</ID>
         <FIRSTNAME>John</FIRSTNAME>
         <LASTNAME>Doe</LASTNAME>
         <MI>D</MI>
         <STREET>1234 Main St.</STREET>
         <CITY>Berthoud</CITY>
         <STATE>CO</STATE>
         <ZIP>80513</ZIP>
       </ROW>
     </ROWSET>';
BEGIN
  -- get context
  context := DBMS_XMLSTORE.NEWCONTEXT('S1.PERSON'); 
  -- clear update settings
  DBMS_XMLSTORE.CLEARUPDATECOLUMNList(context); 
  -- set fields to be updated
  DBMS_XMLSTORE.SETUPDATECOLUMN(context,'ID');  
  DBMS_XMLSTORE.SETUPDATECOLUMN(context,'FIRSTNAME');
  DBMS_XMLSTORE.SETUPDATECOLUMN(context,'LASTNAME');
  DBMS_XMLSTORE.SETUPDATECOLUMN(context,'MI');
  DBMS_XMLSTORE.SETUPDATECOLUMN(context,'STREET');
  DBMS_XMLSTORE.SETUPDATECOLUMN(context,'CITY');
  DBMS_XMLSTORE.SETUPDATECOLUMN(context,'STATE');
  DBMS_XMLSTORE.SETUPDATECOLUMN(context,'ZIP');
  -- insert new records
  rowcount := DBMS_XMLSTORE.INSERTXML(context, xmlData); 
  DBMS_XMLSTORE.CLOSECONTEXT(context);
  DBMS_OUTPUT.PUT_LINE(rowcount || 
    ' new rows inserted in person table.');
END;
/
