By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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 );
1 rows affected
dbms_output:
123189
OK
OK