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-12 19:57:29 |
Message-ID: | 787AE571121E4329BADBBA899E8315C2@dell2 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi!
Thank you.
> 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/
apt-get upgrade postgresql-9.1 returns
Reading package lists... Done
Building dependency tree
Reading state information... Done
You might want to run 'apt-get -f install' to correct these.
The following packages have unmet dependencies:
openssl : Depends: libssl1.0.0 (>= 1.0.1e-2+deb7u5) but it is not
installable
wkhtmltox : Depends: libssl1.0.0 but it is not installable
E: Unmet dependencies. Try using -f.
so it looks like repository is found but not usable ?
>> 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.
Hopefully there is only one Stmt element in single file.
I solved it by moving xpath to select IBAN to main select:
SELECT
(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Acct/ns:Id/ns:IBAN/text()',
xo,nsa))[1]::text AS 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
unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
x,nsa)) as x, nsa, x as xo
FROM t
) Ntry
This references endaaa from single select only. Changing code requires
changing only one line.
Is this OK ?
Andrus.
From | Date | Subject | |
---|---|---|---|
Next Message | Hannes Erven | 2016-08-12 20:05:27 | Re: How to parse xml containing optional elements |
Previous Message | Hannes Erven | 2016-08-12 19:30:56 | Re: How to parse xml containing optional elements |