Insert multiple values from xmltable into database

Question

There's a task to insert values of 'requirement' into table 'agg'. Please tell me how can I do it? Current code here is showing result of just one of the values, first one, to be specific, how can I make it result all three values?

INSERT INTO test
    VALUES (4,xmltype (
   '<export>
    <responsibleOrg>
    <regNum>01731000018</regNum>
    </responsibleOrg>
        <requirements>
            <requirement>
                <code>8403975</code>
                <name>Требование об отсутствии в предусмотренном Федеральным законом № 44-ФЗ реестре недобросовестных поставщиков </name>
            </requirement>
            <requirement>
                <code>8632410</code>
                <name>Единые требования к участникам </name>
            </requirement>
            <requirement>
                <code>8361978</code>
                <name>Иные дополнительные требования к участникам </name>
            </requirement>
        </requirements>
    </export>'));



 DECLARE res NUMBER;
 BEGIN  
 FOR r IN
 (
 SELECT t.id
 FROM test t
 )loop
 sys.DBMS_OUTPUT.put_line('Номер файла №'|| ' '||r.id);
 FOR r2 IN
 (
 SELECT x.*,k.*
 FROM test t,
      xmltable('export/responsibleOrg'
               passing t.DATA
               COLUMNS
              regnum varchar2(50) path 'regNum')k,
      XMLTABLE ('export/requirements/requirement'
                PASSING t.DATA
                COLUMNS 
                        code VARCHAR2(100) PATH 'code',
                        name varchar2(100) path 'name') x
       WHERE t.id = 4
  )loop 

  IF r2.regNum IS NOT NULL THEN

  SELECT COUNT(*)
  INTO res
FROM agg a
WHERE a.regNum = r2.regNum;
IF res = 0 THEN
INSERT INTO agg(REGNUM,code,name)VALUES(r2.regnum,r2.code,r2.name);
END IF;
END IF;
END loop;
END loop;
END;    

Do not mind russian letters, there's no any commands here written with cyrillic.

Answer 1

Seeing as you're already using xmltable to query the xml column, you can easily use that directly in an insert statement, like:

insert into agg (regnum, code, name)
    select t.id,
       k.regnum,
       x.code,
       x.name
from   test t
       cross join xmltable('export/responsibleOrg'
                           passing t.data
                           columns regnum varchar2(50) path 'regNum',
                                   requirements xmltype path 'requirements') k
       cross join xmltable ('export/requirements/requirement'
                            passing t.data
                            columns code number path 'code',
                                    name varchar2(100) path 'name') x;

You could then add any additional filters on the above statement if you need to exclude rows that are already in the table, etc.

Remember that you should think in terms of sets when working with databases - row-by-row processing, such as you were doing, is horrendously inefficient (a bit like getting bricks delivered at the bottom of your drive and then picking them up one at a time and carrying them to the place you're using them. Much better to get the van to deliver them right to that spot instead, right?!)

Answer 2

It is inserting only the first line because there is a count of registers using the regNum field and it will insert the line only if the counter is 0.

As this field has the same value for all the three rows, you should change the counter or remove it.