using sequence in bulk load using listargg

Question

Below is an Oracle select/insert for a bulk load from XML. It turns out that the text in the XML is limited to 80 chars so a listargg is used to group all the text into one row. The problem comes in using the sequence to generate the PK.

insert into rule_table (rule_nbr, rule_text, eff_start_dt, rule_id)
select code as rule_nbr
      , listagg(text, chr(10)) within group (order by line) as rule_text
      , to_date( creationdate, 'yyyy-mm-dd') as eff_start_dt
      , rule_id_seq.nextval
from xmltable('/table/row'
              passing
              xmltype(xml_data)
              columns
                  code varchar2(6) path './code',
                  line varchar2(5) path './line',
                  text varchar2(80) path './text',
                  creationdate varchar2(10) path './date'
         ) xmlt
group by code, creationdate order by code;                 

Sample Input

<table>
  <row>
    <code>000110</code>
    <line>00000</line>
    <text>Amendment I                                                                     </text>
    <date>2019-08-24</date>
  </row>
  <row>
    <code>000110</code>
    <line>00001</line>
    <text>   Congress shall make no law respecting an establishment of religion, or       </text>
    <date>2019-08-24</date>
  </row>
  <row>
    <code>000110</code>
    <line>00002</line>
    <text>prohibiting the free exercise thereof; or abridging the freedom of speech, or of</text>
    <date>2019-08-24</date>
  </row>
  <row>
    <code>000110</code>
    <line>00003</line>
    <text>the press; or the right of the people peaceably to assemble, and to petition    </text>
    <date>2019-08-24</date>
  </row>
  <row>
    <code>000110</code>
    <line>00004</line>
    <text>the government for a redress of grievances.                                     </text>
    <date>2019-08-24</date>
  </row>
  <row>
    <code>000112</code>
    <line>00000</line>
    <text>Amendment II                                                                    </text>
    <date>1986-01-01</date>
  </row>
  <row>
    <code>000112</code>
    <line>00001</line>
    <text>A well-regulated militia, being necessary to the security of a free state, the  </text>
    <date>1986-01-01</date>
  </row>
  <row>
    <code>000112</code>
    <line>00002</line>
    <text>right of the people to keep and bear arms, shall not be infringed.              </text>
    <date>1986-01-01</date>
  </row>
</table>

Answer 1

You could use an IDENTITY column:

CREATE TABLE rule_table (
  rule_nbr VARCHAR2(10),
  rule_text VARCHAR2(4000),
  eff_start_dt DATE,
  rule_id      NUMBER(8,0)
               GENERATED ALWAYS AS IDENTITY
               CONSTRAINT rule_table__rule_id__pk PRIMARY KEY
);

Then:

insert into rule_table (rule_nbr, rule_text, eff_start_dt)
select code as rule_nbr
      , listagg(text, chr(10)) within group (order by line) as rule_text
      , eff_start_dt
from xmltable('/table/row'
              passing
              xmltype('<table>
  <row>
    <code>000110</code>
    <line>00000</line>
    <text>Amendment I                                                                     </text>
    <date>2019-08-24</date>
  </row>
  <row>
    <code>000110</code>
    <line>00001</line>
    <text>   Congress shall make no law respecting an establishment of religion, or       </text>
    <date>2019-08-24</date>
  </row>
  <row>
    <code>000110</code>
    <line>00002</line>
    <text>prohibiting the free exercise thereof; or abridging the freedom of speech, or of</text>
    <date>2019-08-24</date>
  </row>
  <row>
    <code>000110</code>
    <line>00003</line>
    <text>the press; or the right of the people peaceably to assemble, and to petition    </text>
    <date>2019-08-24</date>
  </row>
  <row>
    <code>000110</code>
    <line>00004</line>
    <text>the government for a redress of grievances.                                     </text>
    <date>2019-08-24</date>
  </row>
  <row>
    <code>000112</code>
    <line>00000</line>
    <text>Amendment II                                                                    </text>
    <date>1986-01-01</date>
  </row>
  <row>
    <code>000112</code>
    <line>00001</line>
    <text>A well-regulated militia, being necessary to the security of a free state, the  </text>
    <date>1986-01-01</date>
  </row>
  <row>
    <code>000112</code>
    <line>00002</line>
    <text>right of the people to keep and bear arms, shall not be infringed.              </text>
    <date>1986-01-01</date>
  </row>
</table>')
              columns
                  code         varchar2(6)  path './code',
                  line         varchar2(5)  path './line',
                  text         varchar2(80) path './text',
                  eff_start_dt DATE         path './date'
         ) xmlt
group by code, eff_start_dt order by code; 

Then:

SELECT * FROM rule_table;

Outputs:

RULE_NBR | RULE_TEXT                                                                        | EFF_START_DT | RULE_ID
:------- | :------------------------------------------------------------------------------- | :----------- | ------:
000110   | Amendment I                                                                      | 24-AUG-19    |       1
         |    Congress shall make no law respecting an establishment of religion, or        |              |
         | prohibiting the free exercise thereof; or abridging the freedom of speech, or of |              |
         | the press; or the right of the people peaceably to assemble, and to petition     |              |
         | the government for a redress of grievances.                                      |              |
:------- | :------------------------------------------------------------------------------- | :----------- | ------:
000112   | Amendment II                                                                     | 01-JAN-86    |       2
         | A well-regulated militia, being necessary to the security of a free state, the   |              |
         | right of the people to keep and bear arms, shall not be infringed.               |              |

db<>fiddle here

Update

An alternative is to wrap the sequence in a function:

CREATE FUNCTION next_rule_id RETURN NUMBER
IS
BEGIN
  RETURN rule_id_seq.nextval;
END;
/

and use that in your INSERT statement:

insert into rule_table (rule_nbr, rule_text, eff_start_dt, rule_id)
select code as rule_nbr
      , listagg(text, chr(10)) within group (order by line) as rule_text
      , eff_start_dt
      , next_rule_id()
from xmltable( ... ) xmlt
group by code, eff_start_dt order by code;

db<>fiddle here