ASA SQL Reference
  SQL Functions
    Alphabetical list of functions

XMLAGG function [String]

Function 

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

Syntax 

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

Parameters 

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

Usage 

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.

Standards and compatibility 

• Part of the SQL/XML draft standard.

• Sybase    Not supported by Adaptive Server Enterprise.

See also 

Using the XMLAGG function

Example 

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

SELECT XMLELEMENT( NAME "order",
                   XMLATTRIBUTES( id AS order_id ),
                     ( SELECT XMLAGG(
                         XMLELEMENT(
                           NAME "product",
                           XMLATTRIBUTES( prod_id,
                              quantity
                              AS "quantity_shipped" )
                                        )
                                    )
                      FROM sales_order_items
                    )
                  ) AS products_ordered
FROM sales_order