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.
You can use XMLTABLE function
select xmltable.*
from t,
lateral xmltable(xmlnamespaces('urn:iso:std:iso:20022:tech:xsd:camt.053.001.02' as ns),