Postgresql - How to change xml data type to integer when using xpath in select

Question

I try to select values from one xml field and insert selected values to another table field with integer type.

QUERY:

INSERT INTO "Match" 
select 
unnest(xpath('./game/id/text()', "File"))
FROM "Files"

Select works fine, but when I try to insert, then error occurs:

SQL error:
ERROR:  column "id" is of type integer but expression is of type xml
LINE 3: unnest(xpath('./game/id/text()', "File")),

HINT:  You will need to rewrite or cast the expression.

When I try to change xml type with cast, then I get another error:

SQL error:
ERROR:  cannot cast type xml to integer
LINE 3: cast(unnest(xpath('./game/id/text()', "File"))as integer)

And when I try to change type with XMLSERIALIZE, then another error occurs:

SQL error:
ERROR:  argument of XMLSERIALIZE must not return a set
LINE 3: XMLSERIALIZE(CONTENT unnest(xpath('./game/id/text()', "File"...

How can I insert selected values to another table?

Andomar · Accepted Answer · 2015-12-12 19:55:29Z

9

You start by selecting the text in the XML "id" nodes:

xpath('game/id/text()', col1)

This returns an array, which you unnest, resulting in between zero or more rows:

unnest(xpath('game/id/text()', col1))

A cross join lateral allows you to run unnest(xpath(...)) for every row in the table:

cross join lateral
        unnest(xpath('game/id/text()', col1)) xp(id)

Finally, Postgres can't convert XML to a number. You need an intermediate conversion to text:

select  id::text::int
from    YourTable
cross join lateral
        unnest(xpath('game/id/text()', col1)) xp(id)

Example at SQL Fiddle.

answered Dec 12, 2015 at 19:55

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql - How to change xml data type to integer when using xpath in select

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