Purpose
XMLAGG() obtains a set of XML fragments and returns an aggregated XML document. You can use this function as an aggregate function.
Syntax
XMLAGG(XMLType_instance [ ORDER BY [list of: expr [ASC|DESC] [NULLS {FIRST|LAST}]] ])
Parameters
| Parameter | Description |
|---|---|
| XMLType_instance | The XMLType data, which can be an XMLType column or an expression that returns XMLType data. |
| expr | The name of the column by which the values 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 | Optional. The sorting method of the list.
|
| NULLS {FIRST | LAST} | Optional. The position of NULL in expr after sorting.
|
Return type
The return type is XMLType.
Examples
# Assume that you have created a table named 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 the XMLELEMENT() function to construct an XML fragment.
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 the value of 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