4

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?

Ambran
  • 2,367
  • 4
  • 31
  • 46
  • check this one : http://stackoverflow.com/questions/17799790/using-xpath-to-extract-data-from-an-xml-column-in-postgres – har07 Jan 29 '16 at 09:20
  • @har07, this link was one of the two references in my question... :-) – Ambran Jan 29 '16 at 09:28

1 Answers1

7

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(...))

  • That's great. One more thing, is it possible to retrieve the text without the curly braces surrounding them? – Ambran Jan 29 '16 at 09:32
  • 1
    @Ambran: `xpath()` returns an array, just use the first element. See my edit –  Jan 29 '16 at 09:37