xmlpostgresqlxmltable

Using xmltable in Postgres


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


Solution

  • 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