Postgres to xml Order By

Question

We are executing the following postgres query:

select
AT.id,
AT.goal,
SS.id,
SS.index,
SS.goal,
TK.id,
TK.index,
TK.label,
TK.actions,
TK.type,
TK.group,
TK.blind,
TK.dynamic
from
automations as SS,
automation_sets as AT,
tasks as TK
where
SS.automation_set = 321
AND SS.automation_set = AT.id
AND TK.automation = SS.id
order by
SS.index, TK.index

which returns the following data:

How can use Postgres xml functions to aggregate the data in different XML levels, being the first level AT.ID+AT.goal and the second level SS.ID + SS.goal?

EDIT
We managed to get info we want but still have 1 issue... The SQL used is the following:

SELECT XMLELEMENT(name automation_set,
    XMLATTRIBUTES(ASET_ID as id),
        XMLAGG(xml_task_group))
FROM (SELECT AT.id as ASET_ID,
        XMLELEMENT(name automation,
        XMLATTRIBUTES(SS.goal as id),
            XMLAGG(XMLELEMENT(name task,
            XMLATTRIBUTES(TK.id as task_id, TK.label, TK.actions, TK.type, TK.group, TK.blind, TK.dynamic, TK.index)))) as xml_task_group
from
    automations as SS,
    automation_sets as AT,
    tasks as TK
where
    SS.automation_set = 321
    AND SS.automation_set = AT.id
    AND TK.automation = SS.id
group by 
      AT.id, SS.id
order by 
      SS.index) t
group by t.ASET_ID

but we are unable to get the order by TK.index, which is the task Index. We must make sure that they are in right order and we are unable to do it so far...

Hi Jim, just post an update from my side and with the kind of data I need to extract — C. Figgs
– C. Figgs, Commented Feb 12, 2020 at 11:16

C. Figgs · Accepted Answer · 2020-02-12 14:48:14Z

1

Done, manage to do it...

I'll posted it for reference.

SELECT XMLELEMENT(name automation_set,
    XMLATTRIBUTES(ASET_ID as id),
        XMLAGG(xml_task_group))
FROM (SELECT AT.id as ASET_ID,
        XMLELEMENT(name automation,
        XMLATTRIBUTES(SS.goal as id),
            XMLAGG(XMLELEMENT(name task,
            XMLATTRIBUTES(TK.id as task_id, TK.label, TK.actions, TK.type, TK.group, TK.blind, TK.dynamic, TK.index)) order by TK.index)) as xml_task_group
from
    automations as SS,
    automation_sets as AT,
    tasks as TK
where
    SS.automation_set = 321
    AND SS.automation_set = AT.id
    AND TK.automation = SS.id
group by 
      AT.id, SS.id
order by 
      SS.index) t
group by t.ASET_ID

Thank you again

answered Feb 12, 2020 at 14:48

C. Figgs

314 bronze badges

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

Collectives™ on Stack Overflow

Postgres to xml Order By

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