How to get name of an element using xpath in Postgres-CodePudding

I have tried this

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

As suggested in other question here, but I just get two empty rows as response.

In addition I have tried

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

But it returns only one row with response as bar.

Is there anyway I could get the query to return two rows with result bar, zar using Xpath?

CodePudding user response：

I find xmltable() easier to work with if the output should be rows:

with data (content) as (
  values ('<foo><bar>test</bar><zar>test1</zar></foo>'::xml)
)  
select x.*
from data
  cross join xmltable('/foo/*' passing content
                       columns 
                         item text path 'name()', 
                         value text path '.') as x

Output is:

item | value
----- ------
bar  | test 
zar  | test1

CodePudding user response：

You can use the name() function to extract the tag name:

select xpath('name(/*)', x)
FROM unnest(
        xpath(
           '/foo/*',
           '<foo><bar>test</bar><zar>test1</zar></foo>'
        )
     ) AS xml(x);

 xpath 
═══════
 {bar}
 {zar}
(2 rows)

CodePudding user response：

Use the xpath function text() to extract the elements' contents:

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

 unnest 
--------
 test
 test1
(2 Zeilen)

To list the element names either use a subquery / CTE or use two unnest(), e.g.

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

 unnest 
--------
 bar
 zar
(2 Zeilen)