Get list of nodenames in PostgreSQL XPATH-CodePudding

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

CodePudding user response：

This one works on Postgre 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: /* | /*//*

CodePudding user response：

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