XML Load - Parsing

Hi

I need help to load XML file data into the staging Area. How can I do that.
I am trying to attach the sample XML file and Staging tables definition.

I have tried to load a small xml file using below procedure.

Thanks

DECLARE
    v_bfile   BFILE := BFILENAME('LOB_TEST_DIR', 'COUNTRIES.xml');
    v_clob    CLOB;
BEGIN
    DBMS_LOB.createtemporary(v_clob, TRUE);
    DBMS_LOB.OPEN(v_bfile, DBMS_LOB.lob_readonly);
    DBMS_LOB.loadfromfile(v_clob, v_bfile, DBMS_LOB.lobmaxsize);
    DBMS_OUTPUT.put_line(v_clob);
INSERT INTO LOAD_TAB 
(STUDY_ID,
PLANNED_SCREENED_PER_SITE,
PLANNED_ENROLLED_PER_SITE,
PLANNED_COMPLETE_PER_SITE,
DT_CREATED,DT_MODIFIED,
CRF_PAGES,
NUMBER_OF_EMPLOYEE_VISITS,
COUNTRY_REGION_ID) 
values 
(
                      SELECT TO_NUMBER (EXTRACTVALUE(VALUE(x), '/TABLE/STUDY_ID')) STUDY_ID,
                      TO_NUMBER (EXTRACTVALUE(VALUE(x), '/TABLE/PLANNED_SCREENED_PER_SITE'))PLANNED_SCREENED_PER_SITE,
                      TO_NUMBER (EXTRACTVALUE(VALUE(x), '/TABLE/PLANNED_ENROLLED_PER_SITE'))PLANNED_ENROLLED_PER_SITE,
                      TO_NUMBER (EXTRACTVALUE(VALUE(x), '/TABLE/PLANNED_COMPLETE_PER_SITE'))PLANNED_COMPLETE_PER_SITE,
                      TO_DATE   (EXTRACTVALUE(VALUE(x), '/TABLE/DT_CREATED'), 'dd-MON-rr') DT_CREATED,
                      TO_DATE   (EXTRACTVALUE(VALUE(x), '/TABLE/DT_MODIFIED'), 'dd-MON-rr') DT_MODIFIED,
                      TO_NUMBER (EXTRACTVALUE(VALUE(x), '/TABLE/CRF_PAGES')) CRF_PAGES,
                      TO_NUMBER (EXTRACTVALUE(VALUE(x), '/TABLE/NUMBER_OF_EMPLOYEE_VISITS')) NUMBER_OF_EMPLOYEE_VISITS,
                      TO_NUMBER (EXTRACTVALUE(VALUE(x), '/TABLE/COUNTRY_REGION_ID')) COUNTRY_REGION_ID
                 FROM (SELECT XMLTYPE(v_clob) xml  FROM DUAL), TABLE(XMLSEQUENCE(EXTRACT(xml, '/USER_TABLES/TABLE'))) x) ;   
    COMMIT;
--EXCEPTION
--WHEn OTHERS THEN
--dbms_output.put_line(sqlerrm||'    '||sqlcode) ;
END;
/

Select allOpen in new window

STAGING-TABLES.txt
xml1.txt