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?
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
foo. Not sure if it somehow possible to index using xmltable.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)
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)
