Postgresql: Using xpath to extract data from an XML column -

i've been trying extract values xml same way @erwin brandstetter answered few times accepted answer, doesn't work me:

according this post , this post should work, empty result:

with x ( select    '<attributes xmlns="http://www.gis34.dk">         <bemaerkning displayname="bemærkning">my comment</bemaerkning>         <billede displayname="billede">http://www.gis34.dk/imageserver.ashx?imageid=f484dd2d-a9a0-4764-b2a9-10c9c784867f</billede>     </attributes>'::xml t )  select xpath('/attributes/bemaerkning/text()', t) comment   x 

result: (expected: {my comment})

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

my database version:

postgresql 9.1.3 on x86_64-unknown-linux-gnu, compiled gcc-4.5.real (ubuntu/linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit 

anyone has idea?

your xml defines namespace, , namespace must used in xpath expression.

select xpath('/g:attributes/g:bemaerkning/text()', t, array[array['g','http://www.gis34.dk']]) comment   x 

note third parameter passes two-dimensional array of namespace mappings.

the xpath() function returns array of elements. if know single element (or ever want first one) return first element of array:

select (xpath('/g:attributes/g:bemaerkning/text()', t, array[array['g','http://www.gis34.dk']])[1] comment   x 

note parentheses around function call: (xpath(...))