Using XML 15: XMLAgg

From [1]: You use SQL/XML standard function XMLAgg to construct a forest of XML elements from a collection of XML elements — it is an aggregate function.

Numeric literals are not interpreted as column positions. For example, ORDER BY 1 does not mean order by the first column. Instead, numeric literals are interpreted as any other literals.

I want just try this select

select USERNAME, XMLElement("Schemas",
   XMLAgg(XMLElement("User", username),
      XMLElement("When", to_char(CREATED, 'dd/mm/yyyy hh24:mi:ss')),
      XMLElement("Id", USER_ID),
      XMLElement("OracleOwn ", ORACLE_MAINTAINED)
      )
   ) xml_schemas
from all_users fetch first 3 rows only

Ok. It's failed. This is because I have to choose only one argument

select USERNAME, XMLElement("Schemas",
   XMLAgg(XMLElement("User", username)
      )
   ) xml_schemas
from all_users fetch first 3 rows only

So I have modify the the statement

select XMLElement("Schemas",
   XMLAgg(XMLElement("User", username)
      )
   ) xml_schemas
from all_users fetch first 3 rows only

Click to enlarge

Update

2017/Oct/11 - Modified the statement because I wrote it badly

Reference

[1] https://docs.oracle.com/database/122/ADXDB/generation-of-XML-data-from-relational-data.htm#GUID-971232BA-B52E-44CB-86DA-E182AF7E2ACB

Part14 Index Part16