From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Andrus <kobruleht2(at)hot(dot)ee> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to parse XML in Postgres newer versions also |
Date: | 2019-03-17 13:49:08 |
Message-ID: | CAFj8pRCZyo7BZwweHR236SnTvLCgf9UjEtKOsVwGeNBtPd3mBw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi
ne 17. 3. 2019 v 12:11 odesílatel Andrus <kobruleht2(at)hot(dot)ee> napsal:
> Hi!
>
> In Postgres 9.1.2 script below produces proper results:
>
> 1.34
> 5.56
>
> In Postgres 11 it produces wrong results:
>
> null
> null
>
> How to make it also to work in newer versions on Postgres ?
>
> create temp table t(x xml, nsa text[][]) on commit drop;
> insert into t values(
> '<?xml version="1.0" encoding="UTF-8"?>
> <Document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02">
> <BkToCstmrStmt>
> <Stmt>
> <Ntry>
> <Amt Ccy="EUR">1.34</Amt>
> </Ntry>
> <Ntry>
> <Amt Ccy="EUR">5.56</Amt>
> </Ntry>
> </Stmt>
> </BkToCstmrStmt>
> </Document> '::xml,
> ARRAY[ARRAY['ns','urn:iso:std:iso:20022:tech:xsd:camt.053.001.02']]);
>
> SELECT
> (xpath('Amt/text()', x,nsa))[1]::text::numeric AS tasusumma
> FROM (
> SELECT
> unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
> x,nsa)) as x,
> nsa
> FROM t
> ) Ntry
>
> Andrus.
>
>
This variant is working
postgres=# SELECT
(xpath('/ns:Ntry/ns:Amt/text()', x,nsa))[1]::text::numeric AS tasusumma
FROM (
SELECT
unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
x,nsa)) as x,
nsa
FROM t
) Ntry
;
But I have not a idea, why old code doesn't work. It is little bit strange
so it worked without namespace before Amt tag.
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2019-03-17 14:05:34 | Re: How to parse XML in Postgres newer versions also |
Previous Message | Andrus | 2019-03-17 07:36:22 | How to parse XML in Postgres newer versions also |