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?

@har07, this link was one of the two references in my question... :-) — Ambran
– Ambran, Commented Jan 29, 2016 at 9:28

score 7 · Accepted Answer · 2016-01-29 09:37:20Z

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

edited Jan 29, 2016 at 9:37

answered Jan 29, 2016 at 9:20

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Ambran Over a year ago

That's great. One more thing, is it possible to retrieve the text without the curly braces surrounding them?

user330315 Over a year ago

@Ambran: xpath() returns an array, just use the first element. See my edit

Collectives™ on Stack Overflow

Postgresql: Using xpath to extract data from an XML column

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related