xml - Postgresql - 在 select 中使用 xpath 时如何将 xml 数据类型更改为整数

Question

我尝试从一个 xml 字段中选择值并将所选值插入到另一个整数类型的表字段中。

查询:

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

选择工作正常，但是当我尝试插入时，出现错误:

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.

当我尝试使用强制转换更改 xml 类型时，出现另一个错误:

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

当我尝试使用 XMLSERIALIZE 更改类型时，会发生另一个错误:

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

如何将选定的值插入到另一个表中？

Answer 1

首先选择 XML“id”节点中的文本:

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

这将返回一个数组，您取消嵌套，结果在零行或多行之间:

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

cross join lateral 允许您为表中的每一行运行 unnest(xpath(...)):

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

最后，Postgres 无法将 XML 转换为数字。您需要中间转换为文本:

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

Example at SQL Fiddle.