如何使用PostgreSQL将一对多关系呈现给XML

我的架构中有多个关系.例如,一个包有许多任务组,任务组又有许多任务.所有表都通过多个表链接在一起,例如包含主键和taskgroup的主键. (我知道这不是严格需要的,因为 XML是一对多的,但我想不出更好的结构).

是否可以将查询结果作为XML来反映一对多结构？所以,结果应该是这样的：

<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 XMLELEMENT(name package,XMLAGG(xml_task_group))
FROM (SELECT p.id as pack_id,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

我只是将第一个select复制到第二个的FROM子句中.

如何使用PostgreSQL将一对多关系呈现给XML

猜你在找的Postgre SQL相关文章