title: Oracle - PL/SQL - INSERT ALL with SELECT as VALUES
body: <p>I have a question regarding the code to be used to put 3 tables in relationship, starting from an XML file.</p>
  
  <p>The 3 tables are Organisation, Publication and the Fact table to put them in relationship.</p>
  
  <p>Below the tables structures:</p>
  
  <pre><code>PUBLICATION
  ------------
  CD_PUB (primary key, progressive number automatically generated when a new record has been inserted, thus the value is not taken from the XML file)
  CD_REC 
  
  ORGANISATION
  ------------
  CD_ORG (primary key, progressive number automatically generated when a new record has been inserted, thus the value is not taken from the XML file)
  
  FT_PUB_ORG
  ------------
  CD_FT (primary key, progressive number automatically generated when a new record has been inserted, thus the value is not taken from the XML file)
  CD_ORG (I want to insert here the related code of the Organisation)
  </code></pre>
  
  <p>The code I'm using is:</p>
  
  <pre><code>BEGIN
    FOR i IN 1..10 LOOP
  
  
  
   INSERT INTO WOS_DM_PUBLICATION (
                                  CD_UID 
                                , DT_COVERDATE 
                                , FL_HAS_ABSTRACT 
                                , QT_ISSUE 
                                , DT_PUBMONTH 
                                , CD_PUBTYPE 
                                , DT_PUBYEAR 
                                , DT_SORTDATE 
                                , QT_VOL 
                                , CD_PAGE_BEGIN 
                                , CD_PAGE_END 
                                , CD_PAGE 
                                , QT_PAGE_COUNT 
                                , QT_TITLE_COUNT 
                                , LB_TITLE_SOURCE 
                                , LB_TITLE_SERIES 
                                , LB_TITLE_SOURCE_ABBREV 
                                , LB_TITLE_ABBREV_ISO 
                                , LB_TITLE_ABBREV_11 
                                , LB_TITLE_ABBREV_29 
                                , LB_TITLE_ITEM                
                                , LB_TITLE_BOOK_SERIES 
                                , CD_ACCESSION_NO 
                                , CD_LANG_TYPE 
                                , CD_LANG_NORM 
                                , CD_IDS 
                                , FL_IDS_AVAIL 
                                , CD_BIB_ID 
                                , CD_BIB_PAGECOUNT                                 
                              )
  
  
                     select 
                               RecUid.cd_uid
                             , PubInfo.*
                             , Titles.*
                             , Title.*
                             , Acc_no.*
                             , Lang.*
                             , Lang2.*
                             , Items.*
  
                      from testtable2 t
                      cross join  xmltable(xmlnamespaces(default 'xxxxxxxxxxxxxxxx'),
                       'records/REC'
                        passing t.xml_file 
                        columns 
                                CD_UID varchar2(200) path 'UID',
                                       names xmltype path 'static_data/summary',
                                    identifi xmltype path 'dynamic_data/cluster_related',
                                        lang xmltype path 'static_data/fullrecord_metadata',
                                        item xmltype path 'static_data/item'
                          ) RecUid
  
                        cross join  xmltable(xmlnamespaces(default 'xxxxxxxxxxxxxxxx'),
                       'summary/pub_info'
                        passing RecUid.names
                        columns                           
                             DT_COVERDATE varchar2(20) path '@coverdate',
                          FL_HAS_ABSTRACT varchar2(20) path '@has_abstract',
                                 QT_ISSUE varchar2(20) path '@issue',
                              DT_PUBMONTH varchar2(20) path '@pubmonth',
                               CD_PUBTYPE varchar2(20) path '@pubtype',
                               DT_PUBYEAR varchar2(20) path '@pubyear',
                              DT_SORTDATE varchar2(20) path '@sortdate',
                                   QT_VOL varchar2(20) path '@vol',
                            CD_PAGE_BEGIN varchar2(20) path 'page/@begin',
                              CD_PAGE_END varchar2(20) path 'page/@end',
                                  CD_PAGE varchar2(20) path 'page',
                            CD_PAGE_COUNT varchar2(20) path 'page/@page_count'    
                          ) PubInfo
  
                      cross join  xmltable(xmlnamespaces(default 'xxxxxxxxxxxxxxxx'),
                       'summary/titles'
                         passing RecUid.names
                        columns     
                           QT_TITLE_COUNT varchar2(20) path '@count'
                         ) Titles
  
                      cross join  xmltable(xmlnamespaces(default 'xxxxxxxxxxxxxxxx'),
                       'summary/titles'
                         passing RecUid.names
                        columns                                 
                                 LB_TITLE_SOURCE varchar2(200) path 'title[@type="source"]',
                                 LB_TITLE_SERIES varchar2(200) path 'title[@type="series"]',
                          LB_TITLE_SOURCE_ABBREV varchar2(200) path 'title[@type="source_abbrev"]',
                             LB_TITLE_ABBREV_ISO varchar2(200) path 'title[@type="abbrev_iso"]',
                              LB_TITLE_ABBREV_11 varchar2(200) path 'title[@type="abbrev_11"]',
                              LB_TITLE_ABBREV_29 varchar2(200) path 'title[@type="abbrev_29"]',
                                   LB_TITLE_ITEM varchar2(200) path 'title[@type="item"]',
                            LB_TITLE_BOOK_SERIES varchar2(200) path 'title[@type="book_series"]'
                         ) Title
  
                      cross join  xmltable( xmlnamespaces(default 'xxxxxxxxxxxxxxxx'),
                       'cluster_related/identifiers/identifier[@type="accession_no"]'
                        passing RecUid.identifi
                        columns 
                           CD_ACCESSION_NO varchar2(200) path '@value'
                          )   Acc_no   
  
                      cross join  xmltable(xmlnamespaces(default 'xxxxxxxxxxxxxxxx'),
                       'fullrecord_metadata/languages/language[@type="primary"]'
                        passing RecUid.lang
                        columns 
                           CD_LANG_TYPE varchar2(200) path '.'
                           --CD_LANG_NORM varchar2(200) path 'normalized_languages/language'
                          )   Lang
  
                      cross join  xmltable(xmlnamespaces(default 'xxxxxxxxxxxxxxxx'),
                       'fullrecord_metadata/normalized_languages/language[@type="primary"]'
                        passing RecUid.lang
                        columns 
                           CD_LANG_NORM varchar2(200) path '.'
                          )   Lang2
  
                      cross join  xmltable(xmlnamespaces(default 'xxxxxxxxxxxxxxxx'),
                       'item'
                        passing RecUid.item
                        columns 
                                CD_IDS VARCHAR(200) path 'ids',
                          FL_IDS_AVAIL VARCHAR(2)   path 'ids/@avail',
                             CD_BIB_ID VARCHAR(50)  path 'bib_id',
                      CD_BIB_PAGECOUNT VARCHAR(25)  path 'bib_pagecount'
  
                        )   Items  ;
  
        INSERT     INTO  WOS_DM_ORGANISATION (
                                        LB_LEGAL_NAME 
                                      ,   CD_ADD_NO 
                                      ,   LB_FULL_ADDRESS 
                                      ,   CD_CITY 
                                      ,   CD_STATE 
                                      ,   CD_COUNTRY_NAME 
                           --           , CD_POSTAL_CODE 
                                      ,   CD_ZIP_LOCATION 
                                      , FL_PARENT
                                         )
  
                            select                              
                                      Organis.* 
                                   ,  'Y' FL_PARENT 
  
                            from testtable2 t
                            cross join  xmltable(xmlnamespaces(default 'xxxxxxxxxxxxxxxx'),
                             'records/REC/static_data/fullrecord_metadata/addresses/address_name'
                              passing t.xml_file 
                              columns 
  
                                 LB_LEGAL_NAME varchar2(200) path 'address_spec/organizations/organization',
                                CD_ADD_NO varchar2(200) path 'address_spec/@addr_no',
                                 LB_FULL_ADDRESS varchar2(200) path 'address_spec/full_address',
                                 CD_CITY varchar2(200) path 'address_spec/city',
                                 CD_STATE varchar2(200) path 'address_spec/state',  
                                 CD_COUNTRY_NAME varchar2(200) path 'address_spec/country',
                                -- CD_POSTAL_CODE varchar2(200) path 'address_spec/zip/.'--,
                                 CD_ZIP_LOCAZION varchar2(200) path 'address_spec/@location'
                                 )   Organis ;
    INSERT INTO FT_PUB_ORG_TEST
  
                  (  
                     CD_PUB_ID ,
                     CD_ORG_ID )
                  VALUES
                  (
  
                  WOS_DM_PUBLICATION_id_seq.currval,
                  WOS_DM_ORGANISATION_id_seq.currval);
  
    x := x + 1;
  END LOOP;
     COMMIT;
       END;                  
  </code></pre>
  
  <p>The problem is that with this code I have only the final values in the FT table, but I want all the relationships.
  I've already tryed with INSERT ALL statement but it seems not to work with SELECT as values.</p>
  
  <p>Please consider that the loop was only a try but it doesn't work as I need.</p>