Remove curly braces from xpath output in Postgres

Question

Let's say I'm parsing an XML using this

SELECT xpath('/id/text()', '<id>45687</id>'::xml);

And the output would be

xpath
-------
{45687}

How do I have it output without the curly braces efficiently? And by efficiently, I mean not having to use a regexp_replace() function like

SELECT regexp_replace(xpath('/id/text()', '<id>45687</id>'::xml)::text, '[{}]', '', 'g');

Answer 1

xpath() doesn't return a string, it returns an xml[]. The curly braces are part of the array notation, not part of the data - you're adding them yourself with the text cast.

For a fixed-size array, you can pull the elements out by index:

SELECT (xpath('/id/text()', '<id>45687</id>'::xml))[1]

Generalising this to an arbitrary number of elements, you might do something like this:

SELECT string_agg(x::text, ',')
FROM unnest(xpath('/id/text()', '<id>45687</id>'::xml)) u(x)

Though this may not be more efficient than your original version. The simplest way to do this via string manipulation is probably

SELECT btrim(xpath('/id/text()', '<id>45687</id>'::xml)::text, '{}')

Answer 2

I think I found what seems to be a better way. Since xpath returns what seems like an array result in Postgres, I just case the xpath output to an array and use unnest to extract that value. It makes sense for xpath to output the values with curly braces because there could be many instances of the element that I'm extracting from. So another way to do it would be

SELECT unnest(xpath('/id/text()', '<id>45687</id>'::xml)::varchar[]);