Re: How to parse xml containing optional elements

Hi!

>I couldn't really believe this so I just installed a VM and a 9.1
postgresql just to test this for you.
>It seems you hit a bug in PostgreSQL prior to 9.1.15:
>https://www.postgresql.org/docs/9.1/static/release-9-1-15.html
>"Fix namespace handling in xpath() (Ali Akbar)
>Previously, the xml value resulting from an xpath() call would not have 
>namespace declarations if the namespace declarations were attached to an 
>ancestor element in the input xml value, rather than to the specific 
>element being returned. Propagate the ancestral declaration so that the 
>result is correct when considered in isolation.
>"
>Given your current PG version, the queries will probably work if you remove 
>the "ns:" parts of the first two xpaths like this:

>SELECT
>  (xpath('Amt/text()', x,nsa))[1]::text::numeric AS tasusumma,
>  (xpath('NtryDtls/TxDtls/Refs/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
>But that is not a good solution:
>- when you eventually do upgrade, the query *will* break
>- it depends on the exact location of the namespace declaration in the 
>source document. Your bank might change that in a way that will be still 
>perfectly valid, but break the assumptions made in that workaround.
>So I suggest to upgrade to a supported version of the 9.1 branch from your 
>more than 5 years old build (which should be easy to do).

Psotgres 9.1 run isn Debian Squeeze which is unsupported.
How to upgrade in Debian Squeeze ?

How to add IBAN column to result table? This column has same value for all 
rows.

create temp table t(x xml, nsa text[][]) on commit drop;
insert into t values(
'

  
    
      
        
          XX00221059842412
        
      
      
        150.00
        
          
            
              PV04131
            
          
        
      
      
        0.38
        
          
            
              2016080100178214-2
            
          
        
      
    
  

', ARRAY[ARRAY['ns','urn:iso:std:iso:20022:tech:xsd:camt.053.001.02']]);

SELECT
unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Acct/ns:Id/ns:IBAN/text()', 
x,nsa))::text AS endaaa,
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;

should produce

endaaa            tasusumma  orderinr
XX00221059842412     150.00  PV04131
XX00221059842412       0.38  null

Andrus.