Complete XML Document from Postgres data

Question

Postgres 9.4

I'm using Postgres XML functions to output a nested xml structure and I get how to create the structure I want at the row level:

select xmlelement(
       name row,
       xmlforest(
                 message_id,
                 time_stamp,
                 xmlforest(message_id, clordid, securityid) as block,   
       clordid,
       symbol))
from flat_messages limit 2

giving this output:

<row>
     <message_id>1</message_id>
     <time_stamp>2015-04-23T14:57:40+01:00</time_stamp>
     <block>
         <message_id>1</message_id>
         <clordid>SV9869385</clordid>
         <securityid>GB0008762899</securityid>
     </block>
     <clordid>SV9869385</clordid>
     <symbol>BG.</symbol>
 </row>
 <row>...

 </row>

However, I need to output all the results as a valid XML document with a root element wrapping the whole results set:

<results>
    <row>...</row>
    <row>...</row>
</results>

I can do this by adding the start and end tags after the data is output but I'm sure there must be some way to add the start and end tags to the query. Anyone have any ideas?

Sami Kuhmonen · Accepted Answer · 2015-05-19 09:54:00Z

4

You can use xmlagg() and another xmlelement() to achieve this:

select xmlelement(name results, xmlagg(xmlelement(
   name row,
   xmlforest(
             message_id,
             time_stamp,
             xmlforest(message_id, clordid, securityid) as block,   
   clordid,
   symbol)))
from flat_messages limit 2

answered May 19, 2015 at 9:54

Sami Kuhmonen

31.4k9 gold badges67 silver badges81 bronze badges

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

1 Comment

Royce Over a year ago

Thanks, this is a good pattern to make a query returning an XML values coming from a table.

Collectives™ on Stack Overflow

Complete XML Document from Postgres data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest