Get values from XML using SQL

Question

I am new to SQL and I was wondering how to get the values '123189', 'OK' and 'OK' from XML down below? I was using the SELECT from down below but the parameters (nMaxRate,vMaxRateValid and vMaxRateVariation) stay empty when I call this procedure.

SELECT:

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;

response_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

Use xmltable with the xmlnamespaces clause to specify your namespaces:

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