PL/SQL:如何循环 sql extract() 结果
这是一个任意的例子。这是一个真正的问题,但我无法分享真实的代码。
我有一个没有标准化节点名称的 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 的新手,并且在短短几天内就学会了您在这里看到的所有内容,因此显然该语言的更复杂/微妙的点仍然让我困惑。
您能提供的任何帮助将不胜感激。如果我有任何拼写错误或有任何不清楚之处,请告诉我,以便我进行澄清。
谢谢 问
This is an arbitrary example. It is a real problem, but I can't share real code.
I have an xml string that does not have standardized node names. example:
<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>
some of the element nodes have stuff children, some do not.
This xml is contained within a database table (for argument sake: table1, column1).
I need to loop through this code using pl/sql to get:
1. the name attribute of the date field
2. the id attribute of the date field
3. the name attribute of the first node after the date
4. the id attribute of the first node after the date
5. the name attribute of the second node after the date
6. the id attribute of the second node after the date
I need to do this for the first 4 (arbitrary) rows (sql queries have rownum < 5)
So far I've been trying to get data with
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;
When I run this, I get:
anonymous block completed
LOOPS: 1
DATE: date1
PKDATE: 101
FLDNAME: ele1ele2ele3ele4...ele15
PKFLD: 111121131141...1151
....
So it essentially spits back all the name attributes from the rest of the nodes in that database record mashed together (rather than in a list as I'd hoped).
It does the same for the id's.
Things to note:
- all of the element nodes have widely varying name attributes. They are not simply a list of three character strings with numbers added to the ends (ele1);
- all of the id attributes for every node are vastly different. They are a jumbled string of numbers (10212 for example), the do not go in asc/dsc order, are not consecutive, and are not related by any pattern.
Obviously I can't just loop through all of the element nodes as they're all unique.
I can't figure out how to write an xpath to get "all the nodes after this one".
I'm brand new to pl/sql and have learned everything you see here in just a matter of days, so obviously the more complex / subtle points of the language still elude me.
Any help you can offer would be greatly appreciated. If I've made any typos or have been unclear in any way, please let me know so I can clarify.
Thanks
Q
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您正在寻找 XMLTABLE。
You're looking for XMLTABLE.