Re: How to parse xml containing optional elements

[Hannes Erven <hannes@xxxxxxxx>]

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



Best regards,

	-hannes


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general