How to parse xml array in Oracle

Question

I have a xml with accounts array, if the array has 1 account this code works fine, but if the array has more than one account this code doesn't work. Please help.

select x.acc_num --into res
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://schemas.microsoft.com/2003/10/Serialization/Arrays' as "a"
  ),
  's:Envelope/s:Body/TryGetCardAccountsResponse/TryGetCardAccountsResult/IbanList'
  passing xmltype(
  '<s:Envelope
    xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
    <s:Body>
        <TryGetCardAccountsResponse
            xmlns="http://www.eubank.kz/Bis.Info.ExternalServices.Bank">
            <TryGetCardAccountsResult
                xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                <IbanList
                    xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
                    <a:string>KZ199480018A00166666</a:string>
                    <a:string>KZ519480061A02366666</a:string>
                </IbanList>
                <Status>Ok</Status>
            </TryGetCardAccountsResult>
        </TryGetCardAccountsResponse>
    </s:Body>
</s:Envelope>'
  )
  columns
    acc_num varchar2(255) path 'a:string'
) x;

Answer 1

Try:

select x.acc_num --into res
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://schemas.microsoft.com/2003/10/Serialization/Arrays' as "a"
  ),
  's:Envelope/s:Body/TryGetCardAccountsResponse/TryGetCardAccountsResult/IbanList/a:string'
  passing xmltype(
  '<s:Envelope
    xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
    <s:Body>
        <TryGetCardAccountsResponse
            xmlns="http://www.eubank.kz/Bis.Info.ExternalServices.Bank">
            <TryGetCardAccountsResult
                xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                <IbanList
                    xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
                    <a:string>KZ199480018A00166666</a:string>
                    <a:string>KZ519480061A02366666</a:string>
                </IbanList>
                <Status>Ok</Status>
            </TryGetCardAccountsResult>
        </TryGetCardAccountsResponse>
    </s:Body>
</s:Envelope>'
  )
  columns
    acc_num varchar2(255) path '.'
) x;