PL/SQL：如何循环 sql extract() 结果

Question

这是一个任意的例子。这是一个真正的问题，但我无法分享真实的代码。

我有一个没有标准化节点名称的 xml 字符串。示例：

<row>
  <date name="date1" id="101"></date>
  <element1 name="ele1" id="111">
    <stuff></stuff>
    <stuff></stuff>
    <stuff></stuff>
  </element1>
  <element2 name="ele2" id="121">
  </element2>
  ...
  <element15 name="ele15" id="1151></element15>
</row>

一些元素节点有东西子节点，有些则没有。

该 xml 包含在数据库表中（出于参数考虑：table1、column1）。

我需要使用 pl/sql 循环这段代码来获取： 1.日期字段的name属性 2.日期字段的id属性 3.日期后第一个节点的name属性 4.日期后第一个节点的id属性 5.日期之后的第二个节点的name属性 6. 日期之后的第二个节点的 id 属性

我需要对前 4 个（任意）行执行此操作（sql 查询的 rownum < 5）

到目前为止，我一直在尝试使用

set serveroutput on format word_wrapped;
DECLARE

    x_att_name varchar2(4000);
    x_id varchar2(4000);
    x_oth_name varchar2(4000);
    x_oth_id varchar2(4000);
    aCount number := 1;
    xpath1 varchar2(4000);
    xpath2 varchar2(4000);

BEGIN

    FOR i IN  (
                  SELECT 

                  EXTRACT(column1, '/row/date/@name') as att_name,
                  EXTRACT(column1, '/row/date/@id') as id,
                  EXTRACT(column1, '/row/date/following::*/@name') as other_name,
                  EXTRACT(column1, '/row/date/following::*/@id') as other_id

                  FROM table1
                  WHERE column1is not null and rownum < 5
              )
    LOOP

            x_att_name := i.att_name.getStringVal();
            x_id := i.id.getStringVal();
            x_oth_name := i.other_name.getStringVal();
            x_oth_id := i.other_id.getStringVal();

            dbms_output.put_line('LOOPS: ' || aCount);
            dbms_output.put_line(' DATE: ' || x_att_name);
            dbms_output.put_line(' PKDATE: ' || x_id);
            dbms_output.put_line(' FLDNAME: ' || x_oth_name);
            dbms_output.put_line(' PKFLD: ' || x_oth_id);

            aCount := aCount+1;

    END LOOP;

END;

当我运行此命令时获取数据，我明白了：

anonymous block completed
LOOPS: 1
 DATE: date1
 PKDATE: 101
 FLDNAME: ele1ele2ele3ele4...ele15
 PKFLD: 111121131141...1151
....

所以它本质上是从该数据库记录中的其余节点中吐出所有名称属性，这些属性混合在一起（而不是像我希望的那样在列表中）。

它对 id 执行相同的操作。

注意事项： - 所有元素节点都有广泛不同的名称属性。它们不仅仅是三个字符串的列表，末尾添加了数字 (ele1)； - 每个节点的所有 id 属性都截然不同。它们是一串混乱的数字（例如 10212），不按 asc/dsc 顺序排列，不连续，并且不以任何模式相关。

显然，我不能循环遍历所有元素节点，因为它们都是唯一的。 我不知道如何编写 xpath 来获取“此节点之后的所有节点”。

我是 pl/sql 的新手，并且在短短几天内就学会了您在这里看到的所有内容，因此显然该语言的更复杂/微妙的点仍然让我困惑。

您能提供的任何帮助将不胜感激。如果我有任何拼写错误或有任何不清楚之处，请告诉我，以便我进行澄清。

谢谢 问

Answer 1

您正在寻找 XMLTABLE。

create table so10t(
  id number,
  data xmltype
);

insert into so10t values (1, xmltype(
'<row>
  <date name="date1" id="101"></date>
  <element1 name="ele1" id="111">
    <stuff></stuff>
    <stuff></stuff>
    <stuff></stuff>
  </element1>
  <element2 name="ele2" id="121">
  </element2>
  <element15 name="ele15" id="1151"></element15>
</row>'));
insert into so10t values (2, xmltype(
'<row>
  <date name="date2" id ="102"/>
  <elem23 name="ele23" id="201">
    <whatever/>
  </elem23>
  <elem56 name="ele56" id="402"/>
  <elem112 name="ele112" id="804"/>
</row>'));

declare
  type rec_t is record(
    date_name varchar2(10),
    date_id number,
    first_elem_name varchar2(10),
    first_elem_id number,
    second_elem_name varchar2(10),
    second_elem_id number
  );
  rec rec_t;
  cur sys_refcursor;
begin
  open cur for
    select x.*
      from so10t,
      xmltable('row' passing so10t.data
               columns
               /* 1. the name attribute of the date field */
               date_name varchar2(10) path 'date/@name',
               /* 2. the id attribute of the date field */
               date_id number path 'date/@id',
               /* 3. the name attribute of the first node after the date */
               first_elem_name varchar2(10) path 'date/(following::*)[1]/@name',
               /* 4. the id attribute of the first node after the date */
               first_elem_id number path 'date/(following::*)[1]/@id',
               /* 5. the name attribute of the second node after the date */
               second_elem_name varchar2(10) path 'date/(following::*)[1]/(following::*)[1]/@name',
               /* 6. the id attribute of the second node after the date */
               second_elem_id number path 'date/(following::*)[1]/(following::*)[1]/@id'
               ) x 
      where rownum < 5 ;

  fetch cur into rec;
  while cur%found loop
    dbms_output.put_line(rec.date_name || ';' || rec.date_id || ';' ||
                         rec.first_elem_name || ';' || rec.first_elem_id || ';' ||
                         rec.second_elem_name || ';' || rec.second_elem_id);
    fetch cur into rec;
  end loop;

  close cur;
end;
/