如何使用可选元素解析xml [英] How to parse xml with optional elements

问题描述

需要在ASP：NET 4.6 MVC控制器的Postgres 9.1+中将SEPA ISO XML事务文件解析为平面表。

SEPA ISO XML transactions file needs to be parsed into flat table in Postgres 9.1+ in ASP:NET 4.6 MVC controller.

我在下面尝试了代码，但这产生了错误结果：

I tried code below but this produces wrong result:

endaaa            tasusumma  orderinr
XX00221059842412     150.00  PV04131
XX00221059842412       0.38  PV04131

由于第二行中没有EndToEnd，因此第二行orderinr列中应该为null或空文本：

Since there is no EndToEnd in second row there should be null or empty text in second row orderinr column:

endaaa            tasusumma  orderinr
XX00221059842412     150.00  PV04131
XX00221059842412       0.38  null

如何解决此问题？

解析可以通过其他方式进行如果更合理，则使用xslt样式表进行转换或在客户端ASP.NET 4.6 MVC中使用。

Parsing can done in other ways e.q. using xslt stylesheet for tranformation or in client side ASP.NET 4.6 MVC if this is more reasonable.

create temp table t(x xml, nsa text[][]) on commit drop;
insert into t values(
'<?xml version=''1.0'' encoding=''UTF-8''?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02">
  <BkToCstmrStmt>
    <Stmt>
      <Acct>
        <Id>
          <IBAN>XX00221059842412</IBAN>
        </Id>
      </Acct>
      <Ntry>
        <Amt Ccy="EUR">150.00</Amt>
        <NtryDtls>
          <TxDtls>
            <Refs>
              <EndToEndId>PV04131</EndToEndId>
            </Refs>
          </TxDtls>
        </NtryDtls>
      </Ntry>
      <Ntry>
        <Amt Ccy="EUR">0.38</Amt>
        <NtryDtls>
          <TxDtls>
            <Refs>
              <AcctSvcrRef>2016080100178214-2</AcctSvcrRef>
            </Refs>
          </TxDtls>
        </NtryDtls>
      </Ntry>
    </Stmt>
  </BkToCstmrStmt>
</Document>
', ARRAY[ARRAY['ns','urn:iso:std:iso:20022:tech:xsd:camt.053.001.02']]);

SELECT 
unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Acct/ns:Id/ns:IBAN/text()', x,nsa))::text AS endaaa,
unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry/ns:Amt/text()', x,nsa))::text::numeric AS tasusumma
 , unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry/ns:NtryDtls/ns:TxDtls/ns:Refs/ns:EndToEndId/text()', x,nsa))::text AS orderinr
FROM t;

更新

使用Hannes答案中的代码在Postgres 9.1中返回空行。如何使其在9.1中工作？

Using code from Hannes answer returns empty rows in Postgres 9.1 How to make it work in 9.1 ?

也发布在pgsql.general邮件列表中。

Posted also in pgsql.general mailing list.

推荐答案

您需要先提取所有ns：Ntry元素，然后获取每个元素的数量和EndToEndId：

You need to extract all ns:Ntry elements first, and then get the amount and EndToEndId for each of them:

SELECT 
    endaaa,
    (xpath('ns:Amt/text()', x,nsa))[1]::text::numeric AS tasusumma,
    (xpath('ns:NtryDtls/ns:TxDtls/ns:Refs/ns:EndToEndId/text()', x,nsa))[1] AS orderinr

FROM (
    SELECT (xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Acct/ns:Id/ns:IBAN/text()', x,nsa))[1] as endaaa,
    unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry', x,nsa)) as x, nsa
    FROM t
) Ntry

这篇关于如何使用可选元素解析xml的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！