From: | "Andrus" <kobruleht2(at)hot(dot)ee> |
---|---|
To: | "Hannes Erven" <hannes(at)erven(dot)at> |
Cc: | "pgsql-general" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to parse xml containing optional elements |
Date: | 2016-08-11 09:19:09 |
Message-ID: | 16E023062C764AF0BAC74715DE01FC2E@dell2 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi!
Thank you.
In "PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real
(Debian 4.4.5-8) 4.4.5, 64-bit"
it returns two empty rows. How to make it work in this version ?
In "PostgreSQL 9.5.2, compiled by Visual C++ build 1800, 32-bit" it works.
Andrus.
-----Algsõnum-----
From: Hannes Erven
Sent: Thursday, August 11, 2016 11:51 AM
To: Andrus
Cc: pgsql-general
Subject: Re: [GENERAL] How to parse xml containing optional elements
Hi Andrus,
> 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;
You need to extract all ns:Ntry elements first, and then get the amount
and EndToEndId for each of them:
SELECT
(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 unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
x,nsa)) as x, nsa
FROM t
) Ntry
From | Date | Subject | |
---|---|---|---|
Next Message | Pekka Rinne | 2016-08-11 10:54:42 | Re: upgrade to repmgr3 |
Previous Message | Hannes Erven | 2016-08-11 08:51:26 | Re: How to parse xml containing optional elements |