Remove curly braces from xpath output in Postgres
Let's say I am parsing XML using this
SELECT xpath('/id/text()', '<id>45687</id>'::xml);
And the way out will be
xpath
-------
{45687}
How can I get the result without curly braces? And effectively, I mean not use the regexp_replace () function like
SELECT regexp_replace(xpath('/id/text()', '<id>45687</id>'::xml)::text, '[{}]', '', 'g');
source to share
xpath()
doesn't return a string, it does xml[]
. The curly curly braces are part of the array notation, not part of the data - you add them yourself with text
.
For a fixed size array, you can dump the elements by index:
SELECT (xpath('/id/text()', '<id>45687</id>'::xml))[1]
Generalizing this to an arbitrary number of elements, you can do something like this:
SELECT string_agg(x::text, ',')
FROM unnest(xpath('/id/text()', '<id>45687</id>'::xml)) u(x)
Though it may not be more efficient than your original version. The easiest way to do this with string manipulation is probably
SELECT btrim(xpath('/id/text()', '<id>45687</id>'::xml)::text, '{}')
source to share
I think I found what seems like the best way. Since the xpath returns what appears to be the result of an array in Postgres, I simply output the xpath to an array and use unsest to retrieve that value. It makes sense for the xpath to output the values with curly braces, because there might be many instances of the element that I am retrieving from. So another way to do it would be
SELECT unnest(xpath('/id/text()', '<id>45687</id>'::xml)::varchar[]);
source to share