在xml中使用命名空间时如何通过xmltable解析xml(Oracle)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22219076/
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
How to parse xml by xmltable when using namespace in xml(Oracle)
提问by Zhe Xin
I want to parse a xml string that is a web service response sent from servier, the xml looks like this:
我想解析一个 xml 字符串，它是从服务器发送的 web 服务响应，xml 如下所示：
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<addResponse xmlns="http://tempuri.org/">
<addResult>20</addResult>
</addResponse>
</soap:Body>
</soap:Envelope>
I want to get the value 20 between elements addResult. My plsql code segment looks like following:
我想在元素 addResult 之间获取值 20。我的 plsql 代码段如下所示：
declare
v_xml clob;
begin
v_xml := '<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<addResponse xmlns="http://tempuri.org/">
<addResult>20</addResult>
</addResponse>
</soap:Body>
</soap:Envelope>';
for c in (select results
from xmltable('Envelope/Body/addResponse' passing xmltype(v_xml)
columns results varchar(100) path './addResult')
)
loop
dbms_output.put_line('the result of calculation is : ' || c.results);
end loop;
end;
seems that nothing was printed out, but if I remove the namespace 'soap', the code works well, so can anybody tell me how can I got the value 20 when the xml has namespace?
似乎没有打印出任何内容，但是如果我删除命名空间“soap”，则代码运行良好，所以有人能告诉我当 xml 具有命名空间时如何获得值 20 吗？
回答by A.B.Cade
Based on this answer
基于这个答案
Should be like this:
应该是这样的：
declare
v_xml clob;
begin
v_xml := '<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<addResponse xmlns="http://tempuri.org/">
<addResult>20</addResult>
</addResponse>
</soap:Body>
</soap:Envelope>';
for c in (select results
from xmltable(xmlnamespaces(default 'http://tempuri.org/',
'http://schemas.xmlsoap.org/soap/envelope/' as
"soap" ),
'soap:Envelope/soap:Body/addResponse' passing
xmltype(v_xml) columns results varchar(100) path
'./addResult')) loop
dbms_output.put_line('the result of calculation is : ' || c.results);
end loop;
end;
