xml - 将Postgresql数据转换为XML格式

嗨，我正在尝试使用以下方法将Postgre SQL数据转换为XML格式：

copy(
select xmlroot
(
    xmlelement
    (
        name "warehouses",
        xmlagg
        (
            xmlelement
            (
                name "warehouse",
                xmlelement(name "id",warehouse.w_id),
                xmlelement(name "name",warehouse.w_name),
                xmlelement
                (
                    name "address",
                    xmlelement(name "street",warehouse.w_street),
                    xmlelement(name "city",warehouse.w_city),
                    xmlelement(name "country",warehouse.w_country)
                ),
                xmlelement
                (
                    name "items",
                    xmlagg(
                    xmlelement
                    (
                        name "item",
                        xmlelement(name "id",item.i_id),
                        xmlelement(name "im_id",item.i_im_id),
                        xmlelement(name "name",item.i_name),
                        xmlelement(name "price",item.i_price),
                        xmlelement(name "qty",stock.s_qty)
                    ))
                )
            )
        )
    ), version '1.0" encoding = "utf-8'
) from warehouse inner join stock on warehouse.w_id = stock.w_id
                  inner join item on stock.i_id = item.i_id
) to '/home/cs4221/Desktop/test.xml'

但显示错误消息：
“无法嵌套聚合函数调用”
指向我的第二个xmlagg函数。
为什么不能嵌套调用xmlagg？
如果没有第二个xmlagg，输出如下：

<warehouses>
<warehouse>
    <id>22</id>
    <name>Namekagon</name>
    <address>
        <street>Anniversary</street>
        <city>Singapore</city>
        <country>Singapore</country>
    </address>
    <items>
        <item>
            <id>4</id>
            <im_id>54868007</im_id>
            <name>MECLIZINE HYDROCHLORIDE</name>
            <price>54.49</price>
            <qty>597</qty>
        </item>
    </items>
</warehouse>
<warehouse>
    <id>22</id>
    <name>Namekagon</name>
    <address>
        <street>Anniversary</street>
        <city>Singapore</city>
        <country>Singapore</country>
    </address>
    <items>
        <item>
            <id>5</id>
            <im_id>24658312</im_id>
            <name>Doxycycline Hyclate</name>
            <price>28.99</price>
            <qty>477</qty>
        </item>
    </items>
</warehouse>

仓库id 22有两个项目4和5。我想把它们汇总到同一部分。

最佳答案

每个选择只能有一个xmlagg。
为了解决这个问题，您可以在一个xmlelement中使用一个子选择，并在那里使用下一级xmlagg。
只需删除查询的和处的联接，然后使用以下内容：

  xmlelement
  (
    name "items",
    (
      select xmlagg
      (
        xmlelement
        (
          name "item",
          xmlelement(name "id",item.i_id),
          xmlelement(name "im_id",item.i_im_id),
          xmlelement(name "name",item.i_name),
          xmlelement(name "price",item.i_price),
          xmlelement(name "qty",stock.s_qty)
        )
      )
    )
  )
  from stock where warehouse.w_id = stock.w_id
    join item on stock.i_id = item.i_id)