How to extract value from soapxml?

I have any soapxml like this:

<s:Envelope
    xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
    <s:Body>
        <TryGetCardBalanceResponse
            xmlns="http://www.eubank.kz/Bis.Info.ExternalServices.Bank">
            <TryGetCardBalanceResult
                xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                <Iban i:nil="true"/>
                <Money
                    xmlns:a="http://www.eubank.kz/Bis">
                    <a:Amount>198</a:Amount>
                    <a:Currency>KZT</a:Currency>
                </Money>
                <Status>Ok</Status>
            </TryGetCardBalanceResult>
        </TryGetCardBalanceResponse>
    </s:Body>
</s:Envelope>

need fetch values Amount and currency

Please help, how can i do that?

I tried:

select extractvalue(xmltype('<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"><s:Body><TryGetCardBalanceResponse xmlns="http://www.eubank.kz/Bis.Info.ExternalServices.Bank"><TryGetCardBalanceResult xmlns:i="http://www.w3.org/2001/XMLSchema-instance"><Iban i:nil="true"/><Money xmlns:a="http://www.eubank.kz/Bis"><a:Amount>198</a:Amount><a:Currency>KZT</a:Currency></Money><Status>Ok</Status></TryGetCardBalanceResult></TryGetCardBalanceResponse></s:Body></s:Envelope>'),
                    '/s:Envelope/s:Body/TryGetCardBalanceResponse/TryGetCardBalanceResult/Money/a:Amount',
                    'xmlns:s="http://www.w3.org/2003/05/soap-envelope"
            xmlns="http://www.eubank.kz/Bis.Info.ExternalServices.Bank"
            xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
            xmlns:a="http://www.eubank.kz/Bis"') xml
  from dual

But query returned null

Solution

Your 's' namespace doesn't match:

select extractvalue(xmltype('<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"><s:Body><TryGetCardBalanceResponse xmlns="http://www.eubank.kz/Bis.Info.ExternalServices.Bank"><TryGetCardBalanceResult xmlns:i="http://www.w3.org/2001/XMLSchema-instance"><Iban i:nil="true"/><Money xmlns:a="http://www.eubank.kz/Bis"><a:Amount>198</a:Amount><a:Currency>KZT</a:Currency></Money><Status>Ok</Status></TryGetCardBalanceResult></TryGetCardBalanceResponse></s:Body></s:Envelope>'),
                    '/s:Envelope/s:Body/TryGetCardBalanceResponse/TryGetCardBalanceResult/Money/a:Amount',
--                    'xmlns:s="http://www.w3.org/2003/05/soap-envelope"
                    'xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"
            xmlns="http://www.eubank.kz/Bis.Info.ExternalServices.Bank"
            xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
            xmlns:a="http://www.eubank.kz/Bis"') xml
  from dual
/

XML                           
------------------------------
198

However, extractValue is deprecated so you should be using XMLQuery instead; and as you need more than one value it would be simpler to use XMLTable:

select x.*
from xmltable (
  xmlnamespaces (
      default 'http://www.eubank.kz/Bis.Info.ExternalServices.Bank',
      'http://schemas.xmlsoap.org/soap/envelope/' AS "s",
      'http://www.w3.org/2001/XMLSchema-instance' as "i",
      'http://www.eubank.kz/Bis' as "a"
  ),
  's:Envelope/s:Body/TryGetCardBalanceResponse/TryGetCardBalanceResult/Money'
  passing xmltype('<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"><s:Body><TryGetCardBalanceResponse xmlns="http://www.eubank.kz/Bis.Info.ExternalServices.Bank"><TryGetCardBalanceResult xmlns:i="http://www.w3.org/2001/XMLSchema-instance"><Iban i:nil="true"/><Money xmlns:a="http://www.eubank.kz/Bis"><a:Amount>198</a:Amount><a:Currency>KZT</a:Currency></Money><Status>Ok</Status></TryGetCardBalanceResult></TryGetCardBalanceResponse></s:Body></s:Envelope>')
  columns
    amount number path 'a:Amount',
    currency varchar2(3) path 'a:Currency'
) x;

    AMOUNT CUR
---------- ---
       198 KZT

If your XML is actually coming from a table then you would need a cross join:

select x.*
from your_table t
cross join xmltable (
  xmlnamespaces (
      default 'http://www.eubank.kz/Bis.Info.ExternalServices.Bank',
      'http://schemas.xmlsoap.org/soap/envelope/' AS "s",
      'http://www.w3.org/2001/XMLSchema-instance' as "i",
      'http://www.eubank.kz/Bis' as "a"
  ),
  's:Envelope/s:Body/TryGetCardBalanceResponse/TryGetCardBalanceResult/Money'
  passing xmltype(t.your_column)
  columns
    amount number path 'a:Amount',
    currency varchar2(3) path 'a:Currency'
) x;

or if it's actually already stored as an XMLType rather than a string, just passing t.your_column.