Get list of nodenames in PostgreSQL XPATH

Question

PostgreSQL 14. Trying to extract all the node names from xml element:

SELECT xpath('./*/name()', '<foo><bar>test</bar><zar>test1</zar></foo>')

does not work

SELECT xpath('./*/text()', '<foo><bar>test</bar><zar>test1</zar></foo>')

works

SELECT xpath('name()', unnest(xpath('./*', '<foo><bar>test</bar><zar>test1</zar></foo>'))

offered as solution gives empty list.

example:

SELECT unnest(xpath('//*/text()', '<foo><bar>test</bar><zar>test1</zar></foo>'))

will work

SELECT unnest(xpath('//*/name()', '<foo><bar>test</bar><zar>test1</zar></foo>'))

will not

SELECT xpath('//text()', unnest(xpath('/foo/*', '<foo><bar>test</bar><zar>test1</zar></foo>')))::varchar

works

SELECT xpath('//name()', unnest(xpath('/foo/*', '<foo><bar>test</bar><zar>test1</zar></foo>')))::varchar

does not work what is wrong with name(), local-name(), namespace-uri() functions? How to get a list of nodes within Postgres without parsing it outside? thanks

What about using a regex? regexp_matches('<foo><bar>test</bar><zar>test1</zar></foo>', '(<[a-zA-Z]+>)', 'g'); — user330315
– user330315, Commented Mar 9, 2022 at 12:06
impossible in my case, i'm parsing quite complicated structure with xmltable and json object in one request. This simple example just to illustrate point. — Andrey Kapliev
– Andrey Kapliev, Commented Mar 9, 2022 at 12:24

Andrey Kapliev · Accepted Answer · 2022-03-09 16:53:10Z

1

It seems that this solution can bypass the problem. For some reason direct call of name() function is not working in xpath() function in latest versions of postgres.

SELECT * 
FROM xmltable('//*' PASSING '<foo><bar>text</bar></foo>'
            COLUMNS
            name varchar PATH 'name()'
            )

answered Mar 9, 2022 at 16:53

Andrey Kapliev

217 bronze badges

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

Comments

LMC · Accepted Answer · 2022-03-10 08:00:16Z

0

This one works on Postgres 9.6. Please notice xpath is //*

SELECT xpath('name()', unnest(xpath('//*', '<foo><bar>test</bar><zar>test1</zar></foo>')));

Result

xpath
-----
{foo}
{bar}
{zar}

This XPath also works: /* | /*//*

edited Mar 10, 2022 at 8:00

user330315

answered Mar 9, 2022 at 15:42

LMC

14.4k3 gold badges34 silver badges62 bronze badges

10 Comments

Andrey Kapliev Over a year ago

SELECT x::varchar FROM (SELECT xpath('name()', unnest(xpath('//*', '<foo><bar>test</bar><zar>test1</zar></foo>'))) AS x) AS xt gives empty list in postgres 14

LMC Over a year ago

What do you get from SELECT unnest(xpath('//*', '<foo><bar>test</bar><zar>test1</zar></foo>'));?

Andrey Kapliev Over a year ago

``` xpath |===| |{""}| |{""}| |{""}| |---| ``` sorry for formatting

LMC Over a year ago

Please try the second posted xpath.

Andrey Kapliev Over a year ago

unnest | ------------------------------------------+ <foo><bar>test</bar><zar>test1</zar></foo>| <bar>test</bar> | <zar>test1</zar> |

|

Collectives™ on Stack Overflow

Get list of nodenames in PostgreSQL XPATH

2 Answers 2

Comments

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related