how to use xpath with a namespace in Postgres?-CodePudding

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!

CodePudding user response：

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