Importing Xml Data into Table in Oracle without using Xml functions

Pass the Xml filename parameter to this procedure to import that file. Before running this procedure create the table xmltbl with the fields as the tag name e.g. <Name>Abc</Name> for this Name tag field Name should be there in xmltbl.

CREATE OR REPLACE PROCEDURE Xml_Insert (infile In varchar2)
As
   Vinput     Varchar2 (1000);
   Nstart     Number             := 1;
   Nocr       Number             := 2;
   Nlen       Number;
   Vextract   Varchar2 (1000);
   Nprv       Number             := 0;

   TYPE Rec Is RECORD (
      Field   Varchar2 (100),
      Val     Varchar2 (1000)
   );

   TYPE Tarray Is Table Of Rec
      Index By Binary_integer;

   Ary        Tarray;
   Ncount     Number             := 1;
   Vfield     Varchar2 (100);
   Vval       Varchar2 (1000);
   Vinsert    Varchar2 (2000);
   Ifile      Utl_file.File_type;
BEGIN
   –Dbms_Output.enable (20000);
   Ifile := Utl_File.fopen (‘c:’, infile, ‘r’, 1000);
         — SKIP FIRST LINE
         Utl_File.get_Line (Ifile, Vinput);
      LOOP
      BEGIN
         Utl_File.get_Line (Ifile, Vinput);
         Nlen := Length (Rtrim(Vinput));
         Nstart := 1;
         Nocr := 2;
         Nprv := 0;
   ARY.Delete;
   NCOUNT := 1;
   IF NLEN > 0 Then
         LOOP
            Vextract := Substr (Vinput, Nstart, Instr (Vinput, ‘>’, Nstart, Nocr) – Nprv);

            BEGIN
—               Dbms_Output.put_Line (Vextract);
               Ary (Ncount).Field := Substr (Vextract, 2, Instr (Vextract, ‘>’) – 2);
               Ary (Ncount).Val :=
                  Substr (Vextract,
                          Instr (Vextract, ‘>’) + 1,
                            Instr (Vextract, ‘<‘, Instr (Vextract, ‘>’) + 1, 1)
                          – Instr (Vextract, ‘>’)
                          – 1
                         );
               Ncount := Ncount + 1;
            EXCEPTION 
     When No_Data_Found Then
       Null;
               When Others
               Then
  RAISE;
            END;

            Nprv := Instr (Vinput, ‘>’, Nstart, Nocr);
            Nstart := Instr (Vinput, ‘>’, Nstart, Nocr) + 1;

            IF Nstart >= Nlen
            Then
               EXIT;
            END IF;
         END LOOP;

         BEGIN
            Vinsert := ‘insert into xmltbl (‘;

            For I In 1 .. Ary.Count
            LOOP
               Vinsert := Vinsert || Ary (I).Field || ‘,’;
            END LOOP;

            Vinsert := Rtrim (Vinsert, ‘,’) || ‘) Values (‘;

            For I In 1 .. Ary.Count
            LOOP
               Vinsert := Vinsert || Chr (39) || Ary (I).Val || Chr (39) || ‘,’;
            END LOOP;

            Vinsert := Rtrim (Vinsert, ‘,’) || ‘)’;
—            Dbms_Output.put_Line (Vinsert);

            EXECUTE Immediate (Vinsert);

            COMMIT;
         END;
   END IF;
      EXCEPTION
      When No_Data_Found Then
          IF Utl_File.is_Open (Ifile)
         Then
            Utl_File.fclose (Ifile);
         END IF;
   EXIT;
      When Others
      Then   

         IF Utl_File.is_Open (Ifile)
         Then
            Utl_File.fclose (Ifile);
         END IF;         
               –Dbms_Output.put_Line (Vinsert);
   RAISE;
   END;
      END LOOP;

— process insert stmt thrg array —–
END;
/