具有一个父/多个子关系的Oracle多表插入

Question

我正在尝试开发一个高效的XML解析例程，该例程使用来自具有父/子关系的数据源的多表插入。我希望同时插入一个父记录和多个子记录。不幸的是，我得到的父记录数量与子记录数量相同。

XML看起来像这样，但有数百个父元素和数千个子元素。

<batch id="123" process_date="20171010">
<parent id="101" name="P101" status="active">
<child id="201" name="C201" value="xxx1" />
<child id="202" name="C202" value="xxx2" />
<child id="203" name="C203" value="xxx3" />
<child id="204" name="C204" value="xxx4" />
<child id="205" name="C205" value="xxx5" />
</parent>
<parent id="102" name="P102" status="active">
<child id="211" name="C211" value="yyy1" />
<child id="212" name="C212 value="yyy2" />
<child id="213" name="C213" value="yyy3" />
</parent>
<parent id="103" name="P103" status="suspended">
<child id="221" name="C221" value="zzz1" />
</parent>
</batch>

这些XML文档存储在下表的XML列中：

tblBatchUpload table (batch_upload_id int, xml xmltype)

我想解析这个xml并将其存储到3个表中：

tblBatch (batch_id int, process_date varchar2(8))
tblParent (parent_id int, batch_id int, parent_name varchar2(10), parent_status varchar2(10))
tblChild (child_id int, parent_id int, child_name varchar2(10), child_value varchar2(10))

我知道我可以多次从这个扁平表中提取批次、父记录和子记录，但我真正想做的是一次对这些数据进行多表插入。问题是，当我只想要一个批次记录和唯一的父记录时，我的多表插入是插入与子记录相同数量的批次和父记录。

以下是我的查询尝试：

INSERT ALL
-- always insert the child record
WHEN 1=1 THEN
INTO tblChild (child_id, parent_id, child_name, child_value) 
VALUES (child_id, parent_id, child_name, child_value)
-- only insert parents that don't already exist
WHEN NOT EXISTS (SELECT * FROM tblParent A WHERE A.parent_id = parent_id) THEN
INTO tblParent (parent_id, batch_id, parent_name, parent_status)
VALUES (parent_id, batch_id, parent_name, parent_status)
-- only insert batches that don't already exist
WHEN NOT EXISTS (SELECT * FROM tblBatch A WHERE A.batch_id = batch_id) THEN 
INTO tblBatch (batch_id, process_date) 
VALUES (batch_id int, process_date)
SELECT
t.batch_upload_id
b.batch_id, 
b.process_date,
p.parent_id,
p.parent_name,
p.parent_status,
c.child_id,
c.child_name,
c.child_value
FROM
tbl_batch_upload t,
XMLTABLE ( '/batch' passing t.xml 
columns batch_id int path '@id', 
process_date varchar2(8) path '@process_date', 
parents XMLTYPE PATH 'Parent') b,
XMLTABLE ( '/parent' passing b.parents 
columns parent_id int path '@id', 
parent_name varchar2(10) path '@name', 
parent_status varchar2(10) path '@status', 
children XMLTYPE PATH 'child') p
XMLTABLE ( '/child' passing p.children 
columns child_id int path '@id', 
child_name varchar2(10) path '@name', 
child_value varchar2(10) path '@value') c
WHERE
t.batch_upload_id = :p_batch_upload_id;

如何只插入唯一的批记录和唯一的父记录，同时只解析一次XML？

Answer 1

我找到了一个解决方案。我不得不将FOR ORDINALITY列添加到XMLTABLE中，并将批处理和父级的条件插入基于子行的平凡性：

INSERT ALL
-- always insert the child record
WHEN 1=1 THEN
INTO tblChild (child_id, parent_id, child_name, child_value) 
VALUES (child_id, parent_id, child_name, child_value)
-- first child implies a new parent
WHEN child_ordinal = 1 /* AND NOT EXISTS (SELECT * FROM tblParent A WHERE A.parent_id = parent_id) */ THEN
INTO tblParent (parent_id, batch_id, parent_name, parent_status)
VALUES (parent_id, batch_id, parent_name, parent_status)
-- first parent first child implies a new batch
WHEN parent_ordinal = 1 AND child_ordinal = 1 /* AND NOT EXISTS (SELECT * FROM tblBatch A WHERE A.batch_id = batch_id) */ THEN 
INTO tblBatch (batch_id, process_date) 
VALUES (batch_id int, process_date)
SELECT
t.batch_upload_id
b.batch_id, 
b.process_date,
p.parent_ordinal,
p.parent_id,
p.parent_name,
p.parent_status,
c.child_ordinal,
c.child_id,
c.child_name,
c.child_value
FROM
tbl_batch_upload t,
XMLTABLE ( '/batch' passing t.xml 
columns batch_id int path '@id', 
process_date varchar2(8) path '@process_date', 
parents XMLTYPE PATH 'Parent') b,
XMLTABLE ( '/parent' passing b.parents 
columns 
parent_ordinal FOR ORDINALITY,
parent_id int path '@id', 
parent_name varchar2(10) path '@name', 
parent_status varchar2(10) path '@status', 
children XMLTYPE PATH 'child') p
XMLTABLE ( '/child' passing p.children 
columns
child_ordinal FOR ORDINALITY, 
child_id int path '@id', 
child_name varchar2(10) path '@name', 
child_value varchar2(10) path '@value') c
WHERE
t.batch_upload_id = :p_batch_upload_id;