xml - 如何使用PostgreSQL呈现与XML的一对多关系

我的架构中有几个多对多的关系。例如，一个包有许多任务组，而任务组又有许多任务。所有表通过多对多表链接在一起，例如拥有包的主键和任务组的主键。 (我知道这并不是严格要求的，因为XML是一对多的，但是我想不到更好的结构)。

是否可以将查询结果作为XML反射(reflect)出一对多的结构？因此，结果应如下所示:

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

通过使用XMLELEMENT()和XMLATTRIBUTE()函数来获取所有任务，我已经设法获得了想要的一部分。像这样:

    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

问题是如何将这些任务分为其父表元素？

最佳答案

尝试:

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

这将为您提供您指定的p.id的所有任务组。

然后:

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

这将为您提供您指定的结构。

详细信息:XMLAGG，XML functions

加入的select将如下所示:

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

我只是将第一个选择复制到第二个选择的FROM子句中。