是否可以将查询结果作为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_groupFROM packages pJOIN package_taskgroup pt ON p.ID = pt.package_IDJOIN taskgroups tg on pt.taskgroup_ID = tg.IDJOIN taskgroup_task tt on tg.ID = tt.taskgroup_IDJOIN tasks t on tt.task_ID = t.IDWHERE 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) tGROUP BY pack_ID
我只是将第一个select复制到第二个的FROM子句中.
总结以上是内存溢出为你收集整理的如何使用PostgreSQL将一对多关系呈现给XML全部内容,希望文章能够帮你解决如何使用PostgreSQL将一对多关系呈现给XML所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)