how to use xpath with a namespace in Postgres?

Question

I am having some issues with xpath(). Consider the example XML below:

<data code = '123' platform = 'a' xmlns = "http://www.myexample.com" >
 <payload>
   <title> this is it! </title>
 </payload>
</data>

This is what is stored in my column xml in the database. I am simply trying to retrieve the this is it! text. I have tried many different queries (I think the issue might be the namespace), nothing works.

For instance:

select *, xpath('//my:payload/text()',
                xml::xml,
                ARRAY[ARRAY['my','http://www.myexample.com']]) from mydatabase

returns nothing. Any ideas? Thanks!

The text is in the tag title, not payload , therefor you need '//my:payload/my:title/text()' as the xpath — user330315
– user330315, Commented Oct 31, 2022 at 9:44

Nickname_used · Accepted Answer · 2022-10-31 10:10:29Z

1

for return data from XML i use xmltable

select *
from T
  cross join xmltable(xmlnamespaces ('http://www.myexample.com' as m),
                      '/m:data/m:payload' 
                      passing cast(xml_column as xml)
                      columns title text path 'm:title'
                      ) as x

edited Oct 31, 2022 at 10:10

answered Oct 31, 2022 at 9:47

Nickname_used

4383 silver badges19 bronze badges

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

2 Comments

ℕʘʘḆḽḘ Over a year ago

it seems I cannot use xmltable... only xpath seems to work

user330315 Over a year ago

@ℕʘʘḆḽḘ then you are either using an outdated (unsupported) Postgres version or not using Postgres at all.

Collectives™ on Stack Overflow

how to use xpath with a namespace in Postgres?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related