Convert a XMl Into Table

CREATE OR REPLACE PROCEDURE SP_Ins_(s_partDetails VARCHAR2) AS
XMLType SYS.XMLTYPE;


/******************************************************************************
   NAME:       SP_Ins_Soverignpartinfo
   PURPOSE:    

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        6/2/2014          1. Created this procedure.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     SP_Ins_Soverignpartinfo
      Sysdate:         6/2/2014
      Date and Time:   6/2/2014, 3:40:49 PM, and 6/2/2014 3:40:49 PM
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/

BEGIN
  XMLType := sys.xmltype.CreateXML(s_partDetails);
 
FOR partinfo in
(
  select 
       --Soverignpartinfo.Extract('//DATASET TABLE NAME/MAXAVAILABILITYQTY/text()').getstringval() as  maxavailibilitys,
       Soverignpartinfo.Extract('//DATASET TABLE NAME/PARTNO/text()').getstringval() as partno,
       Soverignpartinfo.Extract('//DATASET TABLE NAME/PRICE/text()').getstringval() as  price,
       Soverignpartinfo.Extract('//DATASET TABLE NAME/CURRENCY/text()').getstringval() as  currency,
       Soverignpartinfo.Extract('//DATASET TABLE NAME/MAXAVAILABILITYQTY/text()').getstringval() as  maxavailibilitys,
       Soverignpartinfo.Extract('//DATASET TABLE NAME/MINQTY/text()').getstringval() as  Minqty,
       Soverignpartinfo.Extract('//DATASET TABLE NAME/AnonymousPrice/text()').getstringval() as  AnonymousPrice
       FROM
        TABLE (XMLSEQUENCE (XMLTYPE.EXTRACT('//NewDataSet/SoverignPartInfo')))Soverignpartinfo
)
loop

DECLARE
      Countint NUMBER;
BEGIN
   SELECT Count(*) INTO Countint   FROM SOVERIGN_PARTINFO
      WHERE PARTNO = TRIM(partinfo.PARTNO);   -- no such number
      
IF Countint = 0 then


INSERT INTO SOVERIGN_PARTINFO(PARTNO,PRICE,CURRENCY,MAXAVAILABILITYQTY,LASTUPDATEDDATE,MINQTY,AnonymousPrice) VALUES
(TRIM(partinfo.PARTNO),TRIM(partinfo.price),TRIM(partinfo.currency),TRIM(partinfo.maxavailibilitys),sysdate,trim(partinfo.Minqty),trim(partinfo.AnonymousPrice));

else
 
UPDATE SOVERIGN_PARTINFO SET PRICE = trim(partinfo.price),CURRENCY=trim(partinfo.currency),MAXAVAILABILITYQTY= trim(partinfo.maxavailibilitys),LASTUPDATEDDATE=sysdate,MINQTY= trim(partinfo.Minqty),AnonymousPrice= trim(partinfo.AnonymousPrice) WHERE trim(PARTNO) = trim(partinfo.PARTNO);

END IF;
END;
End LOOP;
COMMIT;
END SP_Ins_Soverignpartinfo;