Oracle XML ORA-00978: nested group function without GROUP BY

Question

I am trying to generate XML using Oracle's XML functions but I keep getting the ORA-00978: nested group function without GROUP BY error message.

I am new to XML and also Oracle's XML functions so it seems I am missing something major however when looking at examples online I can't understand what I am doing wrong.

SQL:

select
  xmlelement("apiRequest",
    xmlelement("orders",
      xmlagg(
        xmlelement("order",
          xmlelement("no", orders.order_no),
          xmlelement("date", orders.date),
          xmlelement("orderItems",
            xmlagg(
              xmlelement("orderItem",
                xmlelement("position", order_items.item_position)
              )
            )
          )
        )
      )
    )
  )
  as xml
from
  ...

Desired output:

<apiRequest>
  <orders>
    <order>
      <no>1</no>
      <date>04/03/2010</date>
      <orderItems>
        <orderItem>
          <position>1</position>
        </orderItem>
        <orderItem>
          <position>2</position>
        </orderItem>
        <orderItem>
          <position>3</position>
        </orderItem>
        <orderItem>
          <position>4</position>
        </orderItem>
      </orderItems>
    </order>
    <order>
      <no>2</no>
      <date>04/03/2010</date>
      <orderItems>
        <orderItem>
          <position>1</position>
        </orderItem>
        <orderItem>
          <position>2</position>
        </orderItem>
        <orderItem>
          <position>3</position>
        </orderItem>
        <orderItem>
          <position>4</position>
        </orderItem>
      </orderItems>
    </order>
  </orders>
</apiRequest>

Answer 1

When you do a nested aggregation, you need a GROUP BY clause. The first level of aggregation is at the GROUP BY level, then the result of that is aggregated again.

To illustrate, I used the ORDERS and ORDER_ITEMS tables from the OE schema that Oracle provides.

select
  xmlelement("apiRequest",
    xmlelement("orders",
      xmlagg(
        xmlelement("order",
          xmlelement("no", order_id),
          xmlelement("date", oe.orders.order_date),
          xmlelement("orderItems",
            xmlagg(
              xmlelement("orderItem",
                xmlelement("position", oe.order_items.line_item_id)
              )
            )
          )
        )
      )
    )
  )
  as xml
from oe.orders join oe.order_items using(order_id)
group by order_id, order_date;

The other answer uses a scalar subquery, which I would avoid in this case because it executes one recursive SQL for each order instead of simply joining.

Answer 2

You could put second xmlagg in a subquery, it gives desired result:

select 
  xmlelement("apiRequest",
    xmlelement("orders",
      xmlagg(
        xmlelement("order",
          xmlelement("no", orders.order_no),
          xmlelement("date", orders.order_date),
          xmlelement("orderItems",
            (select xmlagg(
              xmlelement("orderItem",
                xmlelement("position", order_items.item_position)
              )
            )
            from order_items where order_no = orders.order_no)
          ))))) as xml
  from orders 

^dbfiddle