PostgreSQL: how to extract value attribute from XML

Question

I am trying to convert the following SQL Server query to Postgresql

 select CAST(CAST('<IncludeSettle/><StartTime value="2019-03-26 08:45:48.780"></StartTime>' as XML).value('(//StartTime/@value)[1]', 'datetime') AS varchar(40)) + ''')';

I have tried converting it to below and got back following error.

 select unnest(xpath('//StartTime/@value', xmlparse(document '<IncludeSettle/><StartTime value="2019-03-26 08:45:48.780"></StartTime>')))

Error:

    ERROR:  invalid XML document
    DETAIL:  line 1: Extra content at the end of the document
    <IncludeSettle/><StartTime value="2010-03-26 08:45:48.780"></StartTim
                               ^
    SQL state: 2200M

As a hack, I had made the following change to make it work.

 select unnest(xpath('//StartTime/@value', xmlparse(document '<tempzz>'||'<IncludeSettle/><StartTime value="2019-03-26 08:45:48.780"></StartTime>'||'</tempzz>')))

Output for Postgresql:

 2019-03-26 08:45:48.780

I am looking for a better solution. Any help really appreciated.

As per the documentation, "The second argument must be a well formed XML document. In particular, it must have a single root node element.". It may be easier to use regex to extract the value you want. Or, do what you're doing, add a root node yourself. I've done exactly that in a few cases in the past where I needed to use xpath but there was no root node. — 404
– 404, Commented Nov 21, 2019 at 9:53

score 1 · Accepted Answer · 2019-11-21 11:56:45Z

1

You can process that by adding the dummy root as you did. The value is already formatted as an ISO timestamp, so you can simply cast it to a timestamp:

But as there is no direct cast from xml to timestamp you need to cast the result of the xpath() to text first.

with data (input) as (
 values ('<IncludeSettle/><StartTime value="2019-03-26 08:45:48.780"></StartTime>')
)
select (xpath('//StartTime/@value', ('<dummy_root>'||input||'</dummy_root>')::xml))[1]::text::timestamp
from data

Collectives™ on Stack Overflow

PostgreSQL: how to extract value attribute from XML

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related