How to render one to many relationships to XML with PostgreSQL

Question

I have several many to many relationships in my schema. For example, a package has many taskgroups, taskgroups in turn have many tasks. All tables are linked together via many to many tables, holding for instance the primary key of package and the primary key of taskgroup. (I know this is not strictly needed, since XML is one to many, but I couldn't think of a better structure).

Is it possible to get the query result as XML, reflecting the one-to-many structure? So, the result should be like this:

<package id=1>
  <taskgroup id=1>
    <task id=1>
    <task id=2>
  </taskgroup>
  <taskgroup id=2>
    <task id=3>
  </taskgroup>
</package>

I have managed to get part of what I want by using the XMLELEMENT() and XMLATTRIBUTE() functions to get all the tasks. Like so:

    SELECT XMLELEMENT(name task,
      XMLATTRIBUTES(p.name as packageName),
        XMLELEMENT(name description, t.description),
        XMLELEMENT(name tutorial,
          XMLELEMENT(name someTaskChild1, t.data1)),
        XMLELEMENT(name objectives,
          XMLELEMENT(name someTaskChild2, t.data2)),
    ) 
    FROM packages p
    INNER JOIN package_taskgroup pt ON p.id = pt.package_id
    INNER JOIN taskgroups tg on pt.taskgroup_id = tg.id
    INNER JOIN taskgroup_task tt on tg.id = tt.taskgroup_id
    INNER JOIN tasks t on tt.task_id = t.id
    WHERE p.id = somePackageId AND tg.id = someTaskGroupId

The question is how to group these tasks into their parent table elements?

I have never used the XMLATTRIBUTES / XMLELEMENT functions in postgres. What does the output look like? Just an ordinary table with xml-elements? — MortenSickel
– MortenSickel, Commented Dec 21, 2012 at 10:21
The output is an xml string. :) I don't have an ORM available for the project I currently work on, and the code is so strongly tied to XML that I pretty much have to provide that. It would be so helpful if the database could actually produce the required xml output. — average joe
– average joe, Commented Dec 21, 2012 at 10:25
This question appears to be better suited for dba.stackexchange.com. — Dave Jarvis
– Dave Jarvis, Commented Oct 3, 2014 at 22:02

Ihor Romanchenko · Accepted Answer · 2012-12-21 14:00:50Z

7

Try:

SELECT p.id as pack_id,
       XMLELEMENT(name taskgroup,
                  XMLATTRIBUTES(tg.id as id),
                  XMLAGG(XMLELEMENT(name task,
                         XMLATTRIBUTES(t.id as id)) as xml_task_group
FROM packages p
JOIN package_taskgroup pt ON p.id = pt.package_id
JOIN taskgroups tg on pt.taskgroup_id = tg.id
JOIN taskgroup_task tt on tg.id = tt.taskgroup_id
JOIN tasks t on tt.task_id = t.id
WHERE p.id = somePackageId 
GROUP BY p.id, tg.id

This will give you all the task groups for the p.id you specified.

Then:

SELECT XMLELEMENT(name package, 
                  XMLATTRIBUTES(pack_id as id),
                  XMLAGG(xml_task_group))
FROM (previous SELECT here)

This will give you the structure you specified.

Details: XMLAGG, XML functions

Joined select will look like this:

SELECT XMLELEMENT(name package, 
                  XMLATTRIBUTES(pack_id as id),
                  XMLAGG(xml_task_group))
FROM (SELECT p.id as pack_id,
             XMLELEMENT(name taskgroup,
                        XMLATTRIBUTES(tg.id as id),
                        XMLAGG(XMLELEMENT(name task,
                                          XMLATTRIBUTES(t.id as id)
                       ))) as xml_task_group
      FROM packages p
      JOIN package_taskgroup pt ON p.id = pt.package_id
      JOIN taskgroups tg on pt.taskgroup_id = tg.id
      JOIN taskgroup_task tt on tg.id = tt.taskgroup_id
      JOIN tasks t on tt.task_id = t.id
      WHERE p.id = somePackageId 
      GROUP BY p.id, tg.id) t
GROUP BY pack_id

I simply copied the first select into FROM clause of the second.

edited Dec 21, 2012 at 14:00

answered Dec 21, 2012 at 10:29

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

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

5 Comments

average joe Over a year ago

I think you've pointed me in the right direction(upvote for that), but I'm still a little confused as to how to join these sub selects together. Will try some more and set yours as the answer if I figure it out.

average joe Over a year ago

Sorry for harping, but: ERROR: column "t.pack_id" must appear in the GROUP BY clause or be used in an aggregate function LINE 2: XMLATTRIBUTES(pack_id as id),

average joe Over a year ago

Added a GROUP BY t.pack_id at the bottom and that seemed to resolve the error. Going over the output now. Note also that there's two missing closing parentheses by ) as xml_task_group.

Ihor Romanchenko Over a year ago

@averagejoe Updated the answer. I haven't tested it initially, so having some simple mistakes is natural.

average joe Over a year ago

Mistakes: yes, I was just pointing them out for others to see if they also found your answer useful. Marked as The Answer. Thank you for the help.

Fatih Çelik · Accepted Answer · 2016-08-19 09:27:43Z

You can try like this:

SELECT 
     T0."Mail",
     T0."UserName", 
     (SELECT xmlagg(xmlelement(name Screens, 
                               XMLELEMENT(name "IN1_ID","IN1."ID"),
                               XMLELEMENT(name "IN1_Name", IN1."Name")
                              )
                    ) 
             FROM "Advertisement"."Screen" IN1 
             WHERE T0."ID" = IN1."PlatformID"
    ) AS Screens 
    FROM "Sales"."Platform" T0

XNL Output will be like this:

<screens>
   <IN1_PlatformID>14</IN1_PlatformID>
   <IN1_Name>My Name</IN1_Name>
</screens>
<screens>
   <IN1_PlatformID>15</IN1_PlatformID>
   <IN1_Name>My Name 2</IN1_Name>
</screens>

at c# side you need to insert a root node to read this.

var doc = new System.Xml.XmlDocument();
doc.LoadXml("<rows>" + row["Screens"].ToString() + "</rows>");

After that, you can convert to dataset to read screens data easily:

var xmlReader = new System.Xml.XmlNodeReader(doc);
var dataSet = new System.Data.DataSet();
dataSet.ReadXml(xmlReader);
foreach (System.Data.DataRow item in dataSet.Tables[0].Rows){
    ...
}

Collectives™ on Stack Overflow

How to render one to many relationships to XML with PostgreSQL

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related