时间戳

首页 > 解决方案 > 如何在 Oracle 中解析 xml 数组

sql - 如何在 Oracle 中解析 xml 数组

问题描述

我有一个带有帐户数组的 xml，如果该数组有 1 个帐户，则此代码工作正常，但如果该数组有多个帐户，则此代码不起作用。请帮忙。

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;

标签： sqlxmloracleparsingxmltype

解决方案

尝试：

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;

推荐阅读