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