使用SQL从XML获取值

Question

我是SQL的新手，我想知道如何从下面的XML中获取值"123189"、"OK"one_answers"OK"？我从下面使用SELECT，但当我调用此过程时，参数(nMaxRate、vMaxRateValidat和vMaxRateVariation(保持为空。

选择：

select extractvalue(Value(t), '/soap:max_rate', ''),
extractvalue(Value(t), '/soap:max_rate_valid', ''),
extractvalue(Value(t), '/soap:max_rate_variation', '') 
into nMaxRate , vMaxRateValid, vMaxRateVariation
from TABLE(xmlSequence(extract(response_xml,  '/soap:check_avg_rateResponse/soap:check_avg_rateResult', 
''))) t;

响应_xml:

<senv:Envelope xmlns:senv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/">
<senv:Body>
<soap:check_avg_rateResponse>
<soap:check_avg_rateResult>
<soap:max_rate>123189</soap:max_rate>
<soap:max_rate_valid>OK</soap:max_rate_valid>
<soap:max_rate_variation>OK</soap:max_rate_variation>
</soap:check_avg_rateResult>
</soap:check_avg_rateResponse>
</senv:Body>
</senv:Envelope>

Answer 1

使用xmltable和xmlnamespaces子句来指定名称空间：

with rws as ( select xmltype ('
<senv:Envelope xmlns:senv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/">
<senv:Body>
<soap:check_avg_rateResponse>
<soap:check_avg_rateResult>
<soap:max_rate>123189</soap:max_rate>
<soap:max_rate_valid>OK</soap:max_rate_valid>
<soap:max_rate_variation>OK</soap:max_rate_variation>
</soap:check_avg_rateResult>
</soap:check_avg_rateResponse>
</senv:Body>
</senv:Envelope>' ) x from dual
) 
select s.* from rws, xmltable (
xmlnamespaces ( 
'http://schemas.xmlsoap.org/soap/envelope/' as "senv",  
'http://schemas.xmlsoap.org/wsdl/soap/' as "soap" 
), 
'/senv:Envelope/senv:Body/soap:check_avg_rateResponse/soap:check_avg_rateResult'
passing rws.x
columns 
max_rate int path 'soap:max_rate',
max_rate_valid varchar2(10) path 'soap:max_rate_valid',
max_rate_variation varchar2(10) path 'soap:max_rate_variation'
) s;

MAX_RATE MAX_RATE_VALID   MAX_RATE_VARIATION   
123189 OK               OK 

Answer 2

您想要使用XMLNAMESPACES和XMLTABLE:

DECLARE
response_xml CLOB := '<senv:Envelope xmlns:senv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/">
<senv:Body>
<soap:check_avg_rateResponse>
<soap:check_avg_rateResult>
<soap:max_rate>123189</soap:max_rate>
<soap:max_rate_valid>OK</soap:max_rate_valid>
<soap:max_rate_variation>OK</soap:max_rate_variation>
</soap:check_avg_rateResult>
</soap:check_avg_rateResponse>
</senv:Body>
</senv:Envelope>';
nMaxRate          NUMBER;
vMaxRateValid     VARCHAR2(10);
vMaxRateVariation VARCHAR2(10);
BEGIN
SELECT max_rate,
max_rate_valid,
max_rate_variation
INTO   nMaxRate,
vMaxRateValid,
vMaxRateVariation
FROM   XMLTABLE(
XMLNAMESPACES(
'http://schemas.xmlsoap.org/soap/envelope/' AS "senv",
'http://schemas.xmlsoap.org/wsdl/soap/' AS "soap"
),
'/senv:Envelope/senv:Body/soap:check_avg_rateResponse/soap:check_avg_rateResult'
PASSING XMLTYPE(response_xml)
COLUMNS
max_rate           NUMBER       PATH './soap:max_rate',
max_rate_valid     VARCHAR2(10) PATH './soap:max_rate_valid',
max_rate_variation VARCHAR2(10) PATH './soap:max_rate_variation'
);
DBMS_OUTPUT.PUT_LINE( nMaxRate );
DBMS_OUTPUT.PUT_LINE( vMaxRateValid );
DBMS_OUTPUT.PUT_LINE( vMaxRateVariation );
END;
/

哪个输出：

123189
OK
OK

db<gt；小提琴这里