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

前端之家收集整理的这篇文章主要介绍了如何使用PostgreSQL将一对多关系呈现给XML前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我的架构中有多个关系.例如,一个包有许多任务组,任务组又有许多任务.所有表都通过多个表链接在一起,例如包含主键和taskgroup的主键. (我知道这不是严格需要的,因为 XML是一对多的,但我想不出更好的结构).

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

  1. <package id=1>
  2. <taskgroup id=1>
  3. <task id=1>
  4. <task id=2>
  5. </taskgroup>
  6. <taskgroup id=2>
  7. <task id=3>
  8. </taskgroup>
  9. </package>

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

  1. 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)),)
  2. FROM packages p
  3. INNER JOIN package_taskgroup pt ON p.id = pt.package_id
  4. INNER JOIN taskgroups tg on pt.taskgroup_id = tg.id
  5. INNER JOIN taskgroup_task tt on tg.id = tt.taskgroup_id
  6. INNER JOIN tasks t on tt.task_id = t.id
  7. WHERE p.id = somePackageId AND tg.id = soMetaskGroupId

问题是如何将这些任务分组到其父表元素中?

尝试:
  1. 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
  2. FROM packages p
  3. JOIN package_taskgroup pt ON p.id = pt.package_id
  4. JOIN taskgroups tg on pt.taskgroup_id = tg.id
  5. JOIN taskgroup_task tt on tg.id = tt.taskgroup_id
  6. JOIN tasks t on tt.task_id = t.id
  7. WHERE p.id = somePackageId
  8. GROUP BY p.id,tg.id

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

然后:

  1. SELECT XMLELEMENT(name package,XMLATTRIBUTES(pack_id as id),XMLAGG(xml_task_group))
  2. FROM (prevIoUs SELECT here)

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

详情:XMLAGG,XML functions

加入选择将如下所示:

  1. SELECT XMLELEMENT(name package,XMLAGG(xml_task_group))
  2. FROM (SELECT p.id as pack_id,XMLATTRIBUTES(t.id as id)
  3. ))) as xml_task_group
  4. FROM packages p
  5. JOIN package_taskgroup pt ON p.id = pt.package_id
  6. JOIN taskgroups tg on pt.taskgroup_id = tg.id
  7. JOIN taskgroup_task tt on tg.id = tt.taskgroup_id
  8. JOIN tasks t on tt.task_id = t.id
  9. WHERE p.id = somePackageId
  10. GROUP BY p.id,tg.id) t
  11. GROUP BY pack_id

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

猜你在找的Postgre SQL相关文章