Using xmltable in Postgres

Question

Does anyone have experience in using XMLTABLE function in postgres they could help me out with. I have a text column in a table that contains XML data that I would like to convert into separate records. A sample of the data is shown below.

<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>

I would like to separate out the QandA's into individual records like this:-

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

having read some online stuff around this I came up with this SQL but received an error

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

Any help would be much appreciated. I'm using PostgreSQL V 11.9

Ftisiot · Accepted Answer · 2021-07-26 11:33:26Z

Your code seems correct, if you try it directy on the string

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' )
;

provides the expected output

                            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)

However, if the same code is run against an empty string like below

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' )
;

It throws the error you mentioned

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

You should focus more on understanding/eliminating from the join the rows with empty XML string

user2699504 · Accepted Answer · 2021-07-26 11:35:16Z

1

Ok, it seems my syntax was correct but there were some null data in my questionandanswers column. All sorted now

answered Jul 26, 2021 at 11:35

user2699504

3251 gold badge7 silver badges21 bronze badges

Collectives™ on Stack Overflow

Using xmltable in Postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related