Postgres data to XML format

Question

I have this in postgres:

copy (select  xmlelement(name atccode, xmlattributes(secondary_label as code), 
xmlelement(name description, secondary_label || ' - ' || label)) 
from study_subject where study_id=28 order by label) 
to 'includes/output10.xml';

It gives me the following in the output10.xml file:

<atccode code="6995">
  <description>6995 - Atkins, Dian</description>
</atccode>
<atccode code="130">
  <description>130 - Bailey, Howard H, MD</description>
</atccode>

How can I get this:

<atclist>
<atccode code="6995">
  <description>6995 - Atkins, Dian</description>
</atccode>
<atccode code="130">
  <description>130 - Bailey, Howard H, MD</description>
</atccode>
</atclist>

select xmlelement(name atclist, xmlagg(xmlelement(name atccode, ...))) from ... — Abelisto
– Abelisto, Commented Oct 7, 2016 at 18:05

Samara Galvão · Accepted Answer · 2017-01-13 21:22:45Z

2

SOLUTION:

COPY (SELECT xmlelement(name atclist, xmlagg(xmlelement(name atccode,
xmlattributes(description as code), 
xmlelement(name description, description)))) 
FROM INVlist) 
TO 'path/output12.xml';

edited Jan 13, 2017 at 21:22

answered Jan 11, 2017 at 19:18

Samara Galvão

1313 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres data to XML format

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related