Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Functions » Alphabetical list of functions

WEEKS function [Date and time] Next Page

XMLAGG function [Aggregate]

Generates a forest of XML elements from a collection of XML values.


XMLAGG( value-expression [ ORDER BY order-by-expression ] )


value-expression    An XML value. The content is escaped unless the data type is XML. The order-by-expression orders the elements returned by the function.

order-by-expression    An expression used to order the XML elements according to the value of this expression.


Any values that are NULL are omitted from the result. If all inputs are NULL, or there are no rows, the result is NULL. If you require a well-formed XML document, you must ensure that your query is written so that the generated XML has a single root element.

Data in BINARY, LONG BINARY, IMAGE, and VARBINARY columns is automatically returned in base64-encoded format when you execute a query that contains XMLAGG.

For an example of a query that uses the XMLAGG function with an ORDER BY clause, see Using the XMLAGG function.

See also
Standards and compatibility

The following statement generates an XML document that shows the orders placed by each customer.

                   XMLATTRIBUTES( ID AS order_id ),
                     ( SELECT XMLAGG(
                           NAME "Products",
                           XMLATTRIBUTES( ProductID, Quantity AS "quantity_shipped" ) ) )
                      FROM SalesOrderItems soi
                      WHERE soi.ID = so.ID
                  ) AS products_ordered
FROM SalesOrders so