I have a question regarding the code to be used to put 3 tables in relationship, starting from an XML file.
The 3 tables are Organisation, Publication and the Fact table to put them in relationship.
Below the tables structures:
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_PUB (I want to insert here the related code of the Publication)
CD_ORG (I want to insert here the related code of the Organisation)
The code I'm using is:
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',
_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;
The insert into WOS_DM_PUBLICATION inserts n rows with a primary key of CD_PUB_ID using a trigger and the WOS_DM_PUBLICATION_id_seq sequence. Similarly, the insert into WOS_DM_ORGANISATION inserts exactly n rows with a primary key of CD_ORG_ID using a trigger and the WOS_DM_ORGANISATION_id_seq sequence. After both inserts run, for i between 1 and n, I want to insert a row into FT_PUB_ORG_TEST with the primary key from the i-th key from each of the two previous insert statements.
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 tried with INSERT ALL statement but it seems not to work with SELECT as values.
Please consider that the loop was only a try but it doesn't work as I need.
If the XML contains enough to form a *natural* key for your parent table, then I'd suggest loading the parent records into a temporary table. For example,
<xml>
<parent id="A">
<parent stuff>
<child id="C1">
<child stuff>
<child id="C2">
<parent id="B">
etc
then you'd extract from the XML, and load into a parent GTT:
PK NATURAL_KEY
parent_seq.nextval A
parent_seq.nextval B
and then when loading the CHILD table(s) you would join to this temporary table on the parent natural key to obtain the parent sequence value you assigned earlier.
If there is nothing in the parent XML that could be called a natural key, that is, you really only have hierarchy of the XML itself, then a workaround could be pull the data into PL/SQL arrays and work from there. Logically speaking this would be:
declare
type parent_array is table of parent%rowtype index by pls_integer;
type child_array is table of child%rowtype index by pls_integer;
p parent_array;
c child_array;
begin
for i in ( [your large query producing ALL data, see below] )
loop
if i.is_first_parent = 'Y' then
if p.count > 10000 then
forall x in 1 .. p.count
insert into PARENT values p(x);
forall x in 1 .. c.count
insert into CHILD values c(x);
p.delete;
c.delete;
end if;
p(p.count+1).pk := parent_seq.nextval;
p(p.count).col1 := i.parent_col1;
p(p.count).col2 := i.parent_col2;
end if;
c(c.count+1).pk := child_seq.nextval;
c(c.count).col1 := i.child_col1;
c(c.count).col2 := i.child_col2;
c(c.count).parent_fk := p(p.count).pk; -- the link
end loop;
end;
where your large query is a flattening of the XML, so the data looks like:
<code>
PARENT CHILD IS_FIRST_PARENT
parent1 child1 Y
parent1 child2
parent1 child3
parent2 child1 Y
parent2 child2
etc
Hope this helps