From: | Hannes Erven <hannes(at)erven(dot)at> |
---|---|
To: | Andrus <kobruleht2(at)hot(dot)ee> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to parse xml containing optional elements |
Date: | 2016-08-12 19:30:56 |
Message-ID: | 93862bb1-56f2-480c-6d60-d3d0ef6574a0@erven.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Andrus,
> Psotgres 9.1 run isn Debian Squeeze which is unsupported.
> How to upgrade in Debian Squeeze ?
A plain "apt-get upgrade postgresql-9.1" does not work?
It might help to enable the postgresql.org APT repository. For
instructions, see here:
https://www.postgresql.org/download/linux/debian/
> How to add IBAN column to result table? This column has same value for
> all rows.
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
Be careful, this will only work when there is exactly one ns:Stmt
element in the document.
Else you will have to build a third query level, first selecting the
ns:Stmt entries, second the IBAN and Ntry from them and third amount and
EndToEndId.
-hannes
From | Date | Subject | |
---|---|---|---|
Next Message | Andrus | 2016-08-12 19:57:29 | Re: How to parse xml containing optional elements |
Previous Message | Alvaro Herrera | 2016-08-12 19:20:35 | Re: Critical failure of standby |