时间戳

首页 > 解决方案 > 在 Postgres 中使用 xmltable

xml - 在 Postgres 中使用 xmltable

问题描述

有没有人有在 postgres 中使用 XMLTABLE 函数的经验，他们可以帮助我。我的表中有一个文本列，其中包含要转换为单独记录的 XML 数据。数据示例如下所示。

<conditions><Condition><name>Patellar luxation</name><VeNom>19808</VeNom><PRF>False</PRF><RUWF>False</RUWF><MaintenanceCosts>1</MaintenanceCosts><ConsequentialLoss>2.5</ConsequentialLoss><GroupMCS>1</GroupMCS><GroupCLS>2.5</GroupCLS><Score>2.5</Score><id>125</id><GroupId>1</GroupId><GroupScore>2.5</GroupScore><exclusionType>None</exclusionType><questions><QandA><question>Has your pet had an operation for this condition?</question><answer>No</answer></QandA><QandA><question>Does your pet have any other bone or joint problems?</question><answer>No</answer></QandA><QandA><question>Has the vet ever recommended that your pet lose weight?</question><answer>No</answer></QandA></questions><LinkedConditions><LinkedCondition Name="Complications with the other patellar / knee" VeNom="" Type="D"/><LinkedCondition Name="Cruciate ligament disease" VeNom="" Type="D"/></LinkedConditions></Condition></conditions>

我想将 QandA 分成单独的记录，如下所示：-

Question                                  Answer
---------                                 -------
Has your pet ....                         No
Does your pet have any other bone ...     No
Has the vet ever recommended ...          No

在阅读了一些有关此的在线资料后，我想出了这个 SQL，但收到了一个错误

with data as
(
select questionsandanswers::xml as qa
from mytable
)
SELECT *
    FROM   data x,
            XMLTABLE('conditions/Condition/questions/QandA'
              PASSING qa
              COLUMNS 
                q     text  PATH 'question',
                a     text  PATH 'answer' )

ERROR:  could not parse XML document
DETAIL:  line 1: Document is empty

^
SQL state: 2200M

任何帮助将非常感激。我正在使用 PostgreSQL V 11.9

标签： xmlpostgresqlxmltable

解决方案

如果您直接在字符串上尝试，您的代码似乎是正确的

with data as (
select '<conditions><Condition><name>Patellar luxation</name><VeNom>19808</VeNom><PRF>False</PRF><RUWF>False</RUWF><MaintenanceCosts>1</MaintenanceCosts><ConsequentialLoss>2.5</ConsequentialLoss><GroupMCS>1</GroupMCS><GroupCLS>2.5</GroupCLS><Score>2.5</Score><id>125</id><GroupId>1</GroupId><GroupScore>2.5</GroupScore><exclusionType>None</exclusionType><questions><QandA><question>Has your pet had an operation for this condition?</question><answer>No</answer></QandA><QandA><question>Does your pet have any other bone or joint problems?</question><answer>No</answer></QandA><QandA><question>Has the vet ever recommended that your pet lose weight?</question><answer>No</answer></QandA></questions><LinkedConditions><LinkedCondition Name="Complications with the other patellar / knee" VeNom="" Type="D"/><LinkedCondition Name="Cruciate ligament disease" VeNom="" Type="D"/></LinkedConditions></Condition></conditions>'::xml val)
 SELECT q,a
    FROM   data x,
            XMLTABLE('conditions/Condition/questions/QandA'
              PASSING val
              COLUMNS 
                q     text  PATH 'question',
                a     text  PATH 'answer' )
;

提供预期的输出

                            q                            | a  
---------------------------------------------------------+----
 Has your pet had an operation for this condition?       | No
 Does your pet have any other bone or joint problems?    | No
 Has the vet ever recommended that your pet lose weight? | No
(3 rows)

但是，如果对空字符串运行相同的代码，如下所示

with data as (
select ''::xml val)
 SELECT q,a
    FROM   data x,
            XMLTABLE('conditions/Condition/questions/QandA'
              PASSING val
              COLUMNS 
                q     text  PATH 'question',
                a     text  PATH 'answer' )
;

它抛出你提到的错误

ERROR:  could not parse XML document
DETAIL:  line 1: Document is empty

您应该更多地专注于理解/消除加入带有空 XML 字符串的行

推荐阅读