oracle plsql: how to parse XML and insert into table

You can load an XML document into an XMLType, then query it, e.g.:

DECLARE  x XMLType := XMLType(    '<?xml version="1.0" ?> <person>   <row>       <name>Tom</name>       <Address>           <State>California</State>           <City>Los angeles</City>       </Address>   </row>   <row>       <name>Jim</name>       <Address>           <State>California</State>           <City>Los angeles</City>       </Address>   </row></person>');BEGIN  FOR r IN (    SELECT ExtractValue(Value(p),'/row/name/text()') as name          ,ExtractValue(Value(p),'/row/Address/State/text()') as state          ,ExtractValue(Value(p),'/row/Address/City/text()') as city    FROM   TABLE(XMLSequence(Extract(x,'/person/row'))) p    ) LOOP    -- do whatever you want with r.name, r.state, r.city  END LOOP;END;

select *  FROM XMLTABLE('/person/row'           PASSING               xmltype('                <person>                   <row>                       <name>Tom</name>                       <Address>                           <State>California</State>                           <City>Los angeles</City>                       </Address>                   </row>                   <row>                       <name>Jim</name>                       <Address>                           <State>California</State>                           <City>Los angeles</City>                       </Address>                   </row>                </person>            ')         COLUMNS              --describe columns and path to them:              name  varchar2(20)    PATH './name',              state varchar2(20)    PATH './Address/State',              city  varchar2(20)    PATH './Address/City'     ) xmlt  ;  

CREATE OR REPLACE PROCEDURE ADDEMP    (xml IN CLOB)ASBEGIN    INSERT INTO EMPLOYEE (EMPID,EMPNAME,EMPDETAIL,CREATEDBY,CREATED)    SELECT         ExtractValue(column_value,'/ROOT/EMPID') AS EMPID       ,ExtractValue(column_value,'/ROOT/EMPNAME') AS EMPNAME       ,ExtractValue(column_value,'/ROOT/EMPDETAIL') AS EMPDETAIL       ,ExtractValue(column_value,'/ROOT/CREATEDBY') AS CREATEDBY       ,ExtractValue(column_value,'/ROOT/CREATEDDATE') AS CREATEDDATE    FROM   TABLE(XMLSequence( XMLType(xml))) XMLDUMMAY;    COMMIT;END;