XMLAGG
The XML aggregate function XMLAGG works on columns. This function is valid where other aggregate functions are valid.
This function accepts one argument, which is aggregated across the groups specified in the GROUP BY clause if that clause is specified.
Syntax
XMLAGG ( <XML_value_expression>
[ ORDER BY <sort_specification_list> ]
[ <XML_returning_clause> ]
)
Remarks
• The aggregation can be ordered with an ORDER BY clause specific to the XML aggregate function. This is independent of the SELECT ORDER BY clause.
• If the argument evaluates to NULL, the result is NULL.
Example (Without ORDER BY)
SELECT CAST (XMLAGG (XMLELEMENT (name Name, ContactLastName))
AS VARCHAR(10000)) "Last Name"
FROM /shared/examples/ds_orders/customers CUSTOMER
WHERE CustomerID < 23
Example (With ORDER BY)
SELECT XMLAGG ((XMLELEMENT(name Details,
XMLATTRIBUTES (ProductID as product),
XMLELEMENT (name orderno, OrderID),
XMLELEMENT (name status, Status),
XMLELEMENT (name price, UnitPrice)))
ORDER BY ProductID ASC, Status ASC, OrderID DESC, UnitPrice ASC)
myOutput
FROM /shared/examples/ds_orders/orderdetails
WHERE ProductID < 20