How to parse xml containing optional elements
От | Andrus |
---|---|
Тема | How to parse xml containing optional elements |
Дата | |
Msg-id | 341AD9B6F0CA404DAAD4BD083979C58D@dell2 обсуждение исходный текст |
Ответы |
Re: How to parse xml containing optional elements
|
Список | pgsql-general |
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: tasusumma orderinr 150.00 PV04131 0.38 PV04131 Since there is no EndToEnd in second row there should be null in second row orderinr column. Correct result is: tasusumma orderinr 150.00 PV04131 0.38 null How to fix this ? 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> <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: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; 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. Posted also in http://stackoverflow.com/questions/38888739/how-to-parse-xml-with-optional-elements Andrus.
В списке pgsql-general по дате отправления: