/*
CREATE TABLE master_table ( invctln  NUMBER,  medbu   VARCHAR2(10) )
/

CREATE TABLE detail_table ( invctln  NUMBER, type  VARCHAR2(10), linecode   NUMBER )
/
*/
DECLARE
  x  XMLTYPE := XMLTYPE('
<SampleFile xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://HDHDW0035:8080/MYFILE.xsd">
<Bill>
<InvCtlN>1</InvCtlN>
<MedBU>ABC</MedBU>
<Lineitem>
<Type>P1</Type>
<LineCode>99214</LineCode>
</Lineitem>
</Bill>
<Bill>
<InvCtlN>2</InvCtlN>
<MedBU>DEF</MedBU>
<Lineitem>
<Type>P2</Type>
<LineCode>99215</LineCode>
</Lineitem>
<Lineitem>
<Type>P3</Type>
<LineCode>99216</LineCode>
</Lineitem>
</Bill>
<Bill>
<InvCtlN>3</InvCtlN>
<MedBU>HJK</MedBU>
<Lineitem>
<Type>P4</Type>
<LineCode>99217</LineCode>
</Lineitem>
</Bill>
</SampleFile>');
BEGIN
  FOR i IN ( SELECT EXTRACTVALUE(VALUE(t), '/Bill/InvCtlN') invctln,
                    EXTRACTVALUE(VALUE(t), '/Bill/MedBU') medbu,
                    EXTRACT(VALUE(t), '/Bill/Lineitem') lineitem_xml
               FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/SampleFile/Bill'))) t )
  LOOP
    INSERT INTO master_table ( invctln, medbu )
    VALUES ( i.invctln, i.medbu );

    FOR j IN ( SELECT EXTRACTVALUE(VALUE(t), '/Lineitem/Type') type,
                      EXTRACTVALUE(VALUE(t), '/Lineitem/LineCode') linecode
                 FROM TABLE(XMLSEQUENCE(EXTRACT(i.lineitem_xml, '/Lineitem'))) t )
    LOOP
      INSERT INTO detail_table ( invctln, type, linecode )
      VALUES ( i.invctln, j.type, j.linecode );
    END LOOP;
  END LOOP;
END;
/

SQL> BEGIN
  2  dbms_xmlschema.registerSchema(
  3        schemaurl => 'http://HDHDW0035:8080/MYFILE.xsd',
  4        schemadoc => '<?xml version="1.0" encoding="ISO-8859-1" ?>
  5  <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">

  6  <!-- <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"> -->

  7  <xs:element name="Bill">
  8  <xs:complexType>
  9  <xs:sequence>
 10  <xs:element ref="InvCtlN"/>
 11  <xs:element ref="MedBU"/>
 12  <xs:element ref="Lineitem" maxOccurs="unbounded"/>
 13  </xs:sequence>
 14  </xs:complexType>
 15  </xs:element>
 16  <xs:element name="InvCtlN">
 17  <xs:simpleType>
 18  <xs:restriction base="xs:byte">
 19  <xs:enumeration value="1"/>
 20  <xs:enumeration value="2"/>
 21  <xs:enumeration value="3"/>
 22  </xs:restriction>
 23  </xs:simpleType>
 24  </xs:element>
 25  <xs:element name="LineCode">
 26  <xs:simpleType>
 27  <xs:restriction base="xs:int">
 28  <xs:enumeration value="99214"/>
 29  <xs:enumeration value="99215"/>
 30  <xs:enumeration value="99216"/>
 31  <xs:enumeration value="99217"/>
 32  </xs:restriction>
 33  </xs:simpleType>
 34  </xs:element>
 35  <xs:element name="Lineitem">
 36  <xs:complexType>
 37  <xs:sequence>
 38  <xs:element ref="Type"/>
 39  <xs:element ref="LineCode"/>
 40  </xs:sequence>
 41  </xs:complexType>
 42  </xs:element>
 43  <xs:element name="MedBU">
 44  <xs:simpleType>
 45  <xs:restriction base="xs:string">
 46  <xs:enumeration value="ABC"/>
 47  <xs:enumeration value="DEF"/>
 48  <xs:enumeration value="HJK"/>
 49  </xs:restriction>
 50  </xs:simpleType>
 51  </xs:element>
 52  <xs:element name="SampleFile">
 53  <xs:complexType>
 54  <xs:sequence>
 55  <xs:element ref="Bill" maxOccurs="unbounded"/>
 56  </xs:sequence>
 57  </xs:complexType>
 58  </xs:element>
 59  <xs:element name="Type">
 60  <xs:simpleType>
 61  <xs:restriction base="xs:string">
 62  <xs:enumeration value="PR2"/>
 63  <xs:enumeration value="PR3"/>
 64  <xs:enumeration value="PRO"/>
 65  </xs:restriction>
 66  </xs:simpleType>
 67  </xs:element>
 68  </xs:schema>',
 69  local => FALSE);
 70  END;
 71  /

PL/SQL procedure successfully completed.

SQL> CREATE TABLE test_xml (
  2    test_xml XMLTYPE
  3  )
  4     XMLTYPE test_xml STORE AS OBJECT RELATIONAL
  5        XMLSCHEMA "http://HDHDW0035:8080/MYFILE.xsd"
  6        ELEMENT "SampleFile"
  7  /

Table created.

SQL> INSERT INTO test_xml ( test_xml )
  2  VALUES
  3  (
  4  XMLTYPE('<SampleFile
  5                   xmlns:xdb="http://xmlns.oracle.com/xdb"
  6                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  7                   xsi:noNamespaceSchemaLocation="http://HDHDW0035:8080/MYFILE.xsd">
  8  <Bill>
  9  <InvCtlN>1</InvCtlN>
 10  <MedBU>ABC</MedBU>
 11  <Lineitem>
 12  <Type>PR2</Type>
 13  <LineCode>99214</LineCode>
 14  </Lineitem>
 15  </Bill>
 16  <Bill>
 17  <InvCtlN>2</InvCtlN>
 18  <MedBU>DEF</MedBU>
 19  <Lineitem>
 20  <Type>PR2</Type>
 21  <LineCode>99215</LineCode>
 22  </Lineitem>
 23  <Lineitem>
 24  <Type>PR3</Type>
 25  <LineCode>99216</LineCode>
 26  </Lineitem>
 27  </Bill>
 28  <Bill>
 29  <InvCtlN>3</InvCtlN>
 30  <MedBU>HJK</MedBU>
 31  <Lineitem>
 32  <Type>PRO</Type>
 33  <LineCode>99217</LineCode>
 34  </Lineitem>
 35  </Bill>
 36  </SampleFile>')
 37  )
 38  /

1 row created.