Oracle 12c XML 从响应中获取价值 [英] Oracle 12c XML get value from response

问题描述

我是 PL/SQL 的新手，我需要使用soap web 服务.我无法从此响应中提取 p-address 值:

I am new to PL/SQL and I need to consume soap web service. I am having trouble extracting p-address value from this response:

response xml:

<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
soap:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" 
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <getAddressForIpResponse xmlns="urn:USR1">
      <p-address xsi:type="xsd:string">11.11.11.11:2222</p-address>
    </getAddressForIpResponse>
  </soap:Body>
</soap:Envelope>

我尝试了 EXTRACTVALUE 的各种组合，但没有成功.任何帮助表示赞赏！

I tried with various combination of EXTRACTVALUE with no luck. Any help is appreciated!

推荐答案

你可以用extractvalue做到这一点:

select extractvalue(
  xmltype(response_string), 
  '/soap:Envelope/soap:Body/getAddressForIpResponse/p-address',
  'xmlns="urn:USR1" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"')
from dual;

但是 extractvalue 是 已弃用;所以你应该使用 XMLQuery:

but extractvalue is deprecated; so you should use XMLQuery:

select XMLQuery(
  'declare default element namespace "urn:USR1";
   declare namespace soap="http://schemas.xmlsoap.org/soap/envelope/";
   /soap:Envelope/soap:Body/getAddressForIpResponse/p-address/text()'
  passing xmltype(response_string)
  returning content).getStringval()
from dual;

无论哪种方式，您都需要提供默认和 soap 命名空间.

Either way you need to supply the default and soap namespaces.

db<>fiddle

如果您要提取多位数据，也可以使用 XMLTable；在这里它没有增加太多，但它会是这样的:

You could also use XMLTable if you're extracting multiple bits of data; here it doesn't add much but it would be something like:

select ip_address
from XMLTable (
  XMLNamespaces (
    default 'urn:USR1',
    'http://schemas.xmlsoap.org/soap/envelope/' as "soap"
  ),
  '/soap:Envelope/soap:Body/getAddressForIpResponse/p-address'
  passing xmltype(response_string)
  columns ip_address varchar2(15) path '.');

db<>fiddle

这篇关于Oracle 12c XML 从响应中获取价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！