xml Oracle Pl/SQL：循环通过 XMLTYPE 节点
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/985894/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Pl/SQL: Loop through XMLTYPE nodes
提问by pistacchio
I have a XMLTYPE with the following content:
我有一个包含以下内容的 XMLTYPE：
<?xml version="1.0"?>
<users>
<user>
<name>user1</name>
</user>
<user>
<name>user2</name>
</user>
<user>
<name>user3</name>
</user>
</users>
How can I loop in PL/SQL through all the elements "user"? Thanks
如何在 PL/SQL 中循环遍历所有元素“user”？谢谢
采纳答案by Vincent Malgrat
You can loop through the elements using EXTRACTand XMLSequence(splits the XML into distinct chunks -- here users) like this:
您可以使用EXTRACTand循环遍历元素XMLSequence（将 XML 拆分为不同的块——这里是用户），如下所示：
SQL> SELECT extractvalue(column_value, '/user/name') "user"
2 FROM TABLE(XMLSequence(XMLTYPE(
3 '<?xml version="1.0"?>
4 <users>
5 <user>
6 <name>user1</name>
7 </user>
8 <user>
9 <name>user2</name>
10 </user>
11 <user>
12 <name>user3</name>
13 </user>
14 </users>').extract('/users/user'))) t;
user
--------
user1
user2
user3
回答by diederikh
You can use XQuery. Check out the select statement below. v_xml_doc is the XMLTYPE variable containing the XML data.
您可以使用 XQuery。查看下面的选择语句。v_xml_doc 是包含 XML 数据的 XMLTYPE 变量。
select name
from XMLTable('for $i in /users/user
return $i'
passing v_xml_doc
columns name varchar2(200) path 'name'
)
回答by Fergus McGlynn
How about this:
这个怎么样：
PROCEDURE xmltest IS
v_userlist XMLType;
v_count NUMBER(38) := 1;
BEGIN
/* define XML variable */
v_userlist := XMLType('<?xml version="1.0"?>
<users>
<user>
<name>user1</name>
</user>
<user>
<name>user2</name>
</user>
<user>
<name>user3</name>
</user>
</users>');
/* for each user, print out their name (each element can be extracted using xpath '//user[1]' '//user[2]' etc) */
WHILE v_userlist.existsNode('//user[' || v_count || ']') = 1 LOOP
dbms_output.put_line(v_userlist.extract('//user[' || v_count || ']/name/text()').getStringVal());
v_count := v_count + 1;
END LOOP;
END;
回答by Trijit
select xt.* from xmltable('/users/user' passing xmltype('<users>
<user>
<name>user1</name>
</user>
<user>
<name>user2</name>
</user>
<user>
<name>user3</name>
</user>
</users>') columns name varchar2(10) path 'name' ) xt
回答by cetor007
ITS VERY GOOD!!
CADENA CLOB;
BEGIN
SELECT CASE
WHEN EXISTSNODE (:NEW.MENSAJE, '/Body') <> 0 THEN 'ERROR'
ELSE NULL
END
INTO :NEW.DESCRIPCION_ERROR
FROM DUAL;
CADENA := :NEW.MENSAJE.EXTRACT ('/Body/xmlOriginal/text()').getStringVal ();
CADENA := REPLACE (CADENA, '<', '<');
CADENA := REPLACE (CADENA, '>', '>');
