oracle - PL/SQL XML 解析为关系表

Question

我正在使用 PL/SQL 过程将值从 XML 插入到关系表中。 XML 文件驻留在 XMLTYPE 列中。
包含 XML 的表 (OFFLINE_XML) 的列是

ID, XML_FILE, STATUS

我想在两个表中插入值，即 DEPARTMENT 和 SECTIONS部门结构如下:-

ID, NAME

SECTIONS 表的结构是:-

ID, NAME, DEPARTMENT_ID

现在有第三个表 (LIST_1)，我想在其中插入上述两个表中已经存在的值。
LIST_1 的结构是:-

ID, DEPARTMENT_ID,DEPARTMENT_NAME,SECTIONS_ID, SECTIONS_NAME

XML 格式如下:-

<ROWSET> 
 <DEPARTMENT>
  <DEPARTMENT_ID>DEP22681352268280797</DEPARTMENT_ID>
  <DEPARTMENT_NAME>myDEPARTMENT</DEPARTMENT_NAME>
  <SECTIONS_ID>6390135666643567</SECTIONS_ID>
  <SECTIONS_NAME>mySection</SECTIONS_NAME>
 </DEPARTMENT>
 <DEPARTMENT>
  <DEPARTMENT_ID>DEP255555555550797</DEPARTMENT_ID>
  <DEPARTMENT_NAME>myDEPARTMENT2</DEPARTMENT_NAME>
  <SECTIONS_ID>63901667779243567</SECTIONS_ID>
  <SECTIONS_NAME>mySection2</SECTIONS_NAME>
 </DEPARTMENT>
</ROWSET>


DECLARE
  BEGIN
    insert all
        into department (id, name)
           values (unit_id, unit_name)
        into sections (id, name, department _id)
          values ( sect_id, sect_name, department _id)
    select department .id as department _id
          , department.name as department_name
           , sect.id as sect_id
          , sect.name as sect_name
    from OFFLINE_XML
         , xmltable('/ROWSET/DEPARTMENT'
                   passing OFFLINE_XML.xml_file
                   columns
                      "ID"  varchar2(20) path 'UNIT_ID'
                      , "NAME"  varchar2(20) path 'UNIT_NAME'
                  ) department
          , xmltable('/ROWSET/DEPARTMENT'
               passing OFFLINE_XML.xml_file
               columns
                  "ID"  varchar2(20) path 'SECTIONS_ID'
                  , "NAME"  varchar2(20) path 'SECTIONS_NAME'
              ) sect
               where status = 3;
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
    dbms_output.put_line('Duplicate='|| department.id );
    --insert into LIST_1 values(ID,DEPARTMENT_ID, SECTIONS_ID, DEPARTMENT_NAME,SECTIONS_NAME);
END;

现在的问题是我如何根据表 DEPARTMENT 和 SECTIONS 中已经存在的主键插入或识别值，然后将现有值插入 LIST_1 表中。--------更新后的成果----------------

我想出了另一个解决方案，但这又给我带来了问题。在下面提到的过程中，游标往往会为每个 xquery 重复。我不知道我该如何处理这个问题..

DECLARE
    department_id varchar2(20);
    department_name varchar2(20);
    sect_id varchar2(20);
    sect_name varchar2(20);
    sections_unit_id varchar2(20);
    var number;
    CURSOR C1 IS
     select
            sect.id as sect_id
          , sect.name as sect_name
          , sect.unit_id  as sections_unit_id
    from OFFLINE_XML
           , xmltable('/ROWSET/DEPARTMENT'
                   passing OFFLINE_XML.xml_file
                   columns
                      "ID"  varchar2(20) path 'UNIT_ID'
                      , "NAME"  varchar2(20) path 'UNIT_NAME'
                  ) DEPARTMENT
          , xmltable('/ROWSET/DEPARTMENT'
               passing OFFLINE_XML.xml_file
               columns
                  "ID"  varchar2(20) path 'SECTIONS_ID'
                  , "NAME"  varchar2(20) path 'SECTIONS_NAME'
                  , "DEPARTMENT_ID" varchar2(20) path 'DEPARTMENT_ID'
              ) sect
               where status = 3;

BEGIN
      FOR R_C1 IN C1 LOOP
      BEGIN
      var :=1;
         --insert into sections_temp_1 (id, name)values ( R_C1.sect_id, R_C1.sect_name);
         -- commit;
           dbms_output.put_line('Duplicate='||var);
      EXCEPTION
         WHEN DUP_VAL_ON_INDEX THEN
         dbms_output.put_line('Duplicate='||R_C1.sect_id);
      END;
      var:=var+1;
      END LOOP;

END;

Answer 1

似乎首先您需要稍微复杂一点的 XQuery 才能从 XMLType 字段中提取行。

不需要单独提取部门和部门，然后再尝试匹配回来。

试试这个变体:

select 
  department_id,
  department_name,
  sections_id,
  sections_name
from 
  OFFLINE_XML xml_list,
  xmltable(
    '
      for $dept in $param/ROWSET/DEPARTMENT
        return $dept
    '
    passing xml_list.xml_file as "param"
    columns
      "DEPARTMENT_ID"   varchar2(100) path '//DEPARTMENT/DEPARTMENT_ID',
      "DEPARTMENT_NAME" varchar2(4000) path '//DEPARTMENT/DEPARTMENT_NAME',
      "SECTIONS_ID"     varchar2(100) path '//DEPARTMENT/SECTIONS_ID',
      "SECTIONS_NAME"   varchar2(4000) path '//DEPARTMENT/SECTIONS_NAME'
  ) section_list
where 
  xml_list.Status = 3

SQL fiddle - 1

之后你得到一个数据集，如果你想查找是否已经存在任何值，它可以通过它的主键(或其他东西 - 取决于所需的逻辑)外部连接到现有表:

select 
  offline_set.offline_xml_id,
  offline_set.department_id,
  offline_set.department_name,
  offline_set.sections_id,
  offline_set.sections_name,
  nvl2(dept.id,'Y', 'N') is_dept_exists, 
  nvl2(sect.id,'Y', 'N') is_sect_exists 
from 
  (  
    [... skipped text of previous query ...]
  ) offline_set,
  department dept,
  sections   sect
where
  dept.id (+) = offline_set.department_id
  and
  sect.id (+) = offline_set.sections_id

SQL fiddle - 2

因为我实际上并不知道这个需求背后的逻辑，所以我无法建议任何 future 的处理指令。但您似乎错过了对 LIST_1 中的 OFFLINE_XML 表的引用，该表需要识别错误/重复的来源。