Postgresql: Using xpath to extract data from an XML column

Question

I've been trying to extract values from XML the same way as @Erwin Brandstetter answered a few times with an accepted answer, but it just doesn't work for me:

According to this post and this post this should work, but I just get an empty result:

WITH x AS ( SELECT
   '<Attributes xmlns="http://www.gis34.dk">
        <bemaerkning displayName="Bemærkning">Beatrix</bemaerkning>
        <billede displayName="Billede">https://vignette.wikia.nocookie.net/killbill/images/3/39/The_Bride.jpg</billede>
    </Attributes>'::xml AS t
)

SELECT xpath('/Attributes/bemaerkning/text()', t) as comment
FROM   x

Result: (expected: {My comment})

comment
xml[]
-------
{}

My database version:

PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit

Anyone has an idea?

Answer 1

Your XML defines a namespace, and that namespace must be used in the xpath expression.

SELECT xpath('/g:Attributes/g:bemaerkning/text()', t, array[array['g','http://www.gis34.dk']]) as comment
FROM   x

Note the third parameter that passes a two-dimensional array of namespace mappings.

The xpath() function returns an array of elements. If you know you only get a single element (or only ever want the first one) you just return the first element of the array:

SELECT (xpath('/g:Attributes/g:bemaerkning/text()', t, array[array['g','http://www.gis34.dk']])[1] as comment
FROM   x

Note the parentheses around the function call: (xpath(...))