Purpose
This function takes a set of XML fragments and returns an aggregated XML document. It can be used as an aggregate function.
Syntax
XMLAGG(XMLType_instance [ ORDER BY [list of: expr [ASC|DESC] [NULLS {FIRST|LAST}]] ])
Parameters
| Field | Description |
|---|---|
| XMLType_instance | Specifies data of the XMLType type, which can be an XMLType column or an expression that returns data of the XMLType type. |
| expr | Specifies the column name corresponding to the value to be sorted. Note Columns cannot be specified by their position in the result set. For example, ORDER BY 1 does not sort the data by the first column. |
| ASC | DESC | Specifies the sort order. This is an optional parameter.
|
| NULLS {FIRST | LAST} | Specifies the position of NULL values in the sorted result. This is an optional parameter.
|
Return type
XMLType
Examples
# Assume that the employees table has been created.
obclient> SELECT * FROM EMPLOYEES;
+------+-----------+------+
| ID | LAST_NAME | SAL |
+------+-----------+------+
| 1 | CLARK | 2750 |
| 2 | KING | 5300 |
| 3 | MILLER | 1600 |
| 4 | ADAMS | 1400 |
+------+-----------+------+
4 rows in set
# Build XML fragments by using the XMLELEMENT function.
obclient> SELECT XMLELEMENT(EVALNAME LAST_NAME, SAL) AS "RESULT" FROM EMPLOYEES;
+------------------------+
| RESULT |
+------------------------+
| <CLARK>2750</CLARK>
|
| <KING>5300</KING>
|
| <MILLER>1600</MILLER>
|
| <ADAMS>1400</ADAMS>
|
+------------------------+
4 rows in set
# Use XMLAGG to sort and aggregate the result by SAL.
obclient> SELECT XMLAGG(XMLELEMENT(EVALNAME LAST_NAME, SAL) ORDER BY SAL) AS "RESULT" FROM EMPLOYEES;
+----------------------------------------------------------------------------------+
| RESULT |
+----------------------------------------------------------------------------------+
| <ADAMS>1400</ADAMS>
<MILLER>1600</MILLER>
<CLARK>2750</CLARK>
<KING>5300</KING>
|
+----------------------------------------------------------------------------------+
1 row in set