Purpose
XMLAGG() obtains a set of XML fragments and returns an aggregated XML document. You can use it as an aggregate function.
Syntax
XMLAGG(XMLType_instance [ ORDER BY [list of: expr [ASC|DESC] [NULLS {FIRST|LAST}]] ])
Parameters
| Parameter | Description |
|---|---|
| XMLType_instance | The XML-expression arguments to be aggregated. Any arguments that return NULL are dropped from the result. |
| expr | The name of the column by which the aggregated elements are sorted. Notice: You cannot use a number to indicate the position of a column. For example, ORDER BY 1 does not indicate to sort data by the first column. |
| ASC | DESC | The sorting method of the list. This parameter is optional.
|
| NULLS {FIRST | LAST} | The position of NULL in expr after sorting. This parameter is optional.
|
Return type
The return type is CLOB.
Examples
Query all data from the existing table employees.
obclient> SELECT * FROM EMPLOYEES;
+------+-----------+------+
| ID | LAST_NAME | SAL |
+------+-----------+------+
| 1 | CLARK | 2750 |
| 2 | KING | 5300 |
| 3 | MILLER | 1600 |
| 4 | ADAMS | 1400 |
+------+-----------+------+
4 rows in set
Use XMLELEMENT to construct XML fragments.
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 the preceding results by SAL and aggregate them.
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