CODEWITHSUNDEEP
postgresql
user4650542
user4650542

Reputation:

Complete XML Document from Postgres data

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?

Upvotes: 1

Views: 1466

Answers (1)

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31193

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

Upvotes: 4

Related Questions