Oracle XMLAGG

Introduction to Oracle XMLAGG

We can use the XMLAGG function in the oracle database to aggregate the collection of fragments of XML. This function returns the value which will be an aggregated document in XML and if any of the arguments that are passed to this function evaluate to NULL then the argument is skipped or dropped out from the final result. This function behaves in the same way as that of SYS_XMLAGG. But there is only one difference between SYS_XMLAGG and XMLAGG which is that the XMLAGG function does not accept the XMLFORMAT object for the purpose of formatting the result though it returns the collection of the nodes as the result. One more difference is that the resultant of XMLAGG is not enclosed in the element tags as in the case of SYS_XMLAGG. The number literals mentioned in the ORDER BY clause are not interpreted by the oracle database as column positions as in other cases but they are referred to as number literals themselves.

Syntax

The syntax of the XMLAGG function is as shown below –

XMLAGG(<XML_ELEMENT> ORDER BY <VALUE>)

The syntax can also be understood from the following figure –

As the XMLAGG function returns the result in a single row or cell to wrap around all the element tags inside a single parent tag we can optionally make the use of XML_ELEMENT which will help to get all the result enclosed in a new parent tag or inside a single element which is created by it. When the strings are to be aggregated then we can use the “.extract(‘//text()’)” which will keep the string together in XML and the rtrim() function can be used along with it to get rid of the trailing commas or spaces. When instead of strings we are about to aggregate the CLOB values using XMLAGG function then we can make the use of xmlparse which will accept the XML text and will transform it into a datatype of XMLTYPE. The use of the ORDER BY clause is completely optional in nature and can be used to order the values of XML elements that are being concatenated by using the XMLAGG function.

Examples of Oracle XMLAGG

Let us take the example of the customer data of a particular brand having multiple stores and multiple departments in each of the stores. The table is created and the data is inserted in it which can be done by using the following query –

DROP TABLE customers;
COMMIT;
CREATE TABLE customers
( customer_id     NUMBER(6)
, f_name     VARCHAR2(20)
, l_name      VARCHAR2(25)
, email_id          VARCHAR2(40)
, mobile_number   VARCHAR2(20)
, purchase_date      DATE
, store_id       VARCHAR2(20)
, bill_amount           NUMBER(8,2)
, salesman_id   NUMBER(6)
, department_id  NUMBER(4)
) ;
CREATE UNIQUE INDEX cust_id_pk ON customers (customer_id) ;
COMMIT;

There are in all 14 different customers data being inserted into the customer’s table that is created using the INSERT INTO statements whose contents are as shown below –

SELECT department_id,
rtrim(xmlagg(xmlelement(customerData,f_name
|| ' '
|| l_name , ', ')
ORDER BY f_name).extract('//text()').getstringval(), ',') AS "names"
FROM customers
GROUP BY department_id;

The XML element used in the above statement will lead to the creation of a new xml element called customer data. We can give any name to this xml element being created which will contain the concatenated value of the f_name, l_name, and the comma between each of the concatenated values. The use of XMLAGG, in this case, gives rise to the creation of the XML snippet which is the aggregation of the customer data XML elements. The use of rtrim() removes all the spaces and the trailing commas while.extract helps to keep the string together as a single unit. The execution of the above query will give the following output along with all the grouped result set according to the departments of the store as shown below –

In XML format, the output of the execution of the above query will be as follows along with all the grouped result set according to the departments of the store –

We can even make use of the XMLAGG function along with the CLOB datatype values rather than the string values by using the XMLPARSE which takes the specified XML values as text and further converts them to XMLTYPE data type.

SELECT department_id,
xmlagg(xmlparse(content f_name
|| ' '
|| l_name wellformed)
ORDER BY f_name).getclobval()
FROM customers
GROUP BY department_id;

The execution of the above query will give the following output along with all the grouped result set according to the departments of the store but without the commas between the aggregated values as shown below –

In XML format, the output of the execution of the above query will be as follows along with all the grouped result set according to the departments of the store –

We can even use the JSON_ARRAYAGG instead of the XMLAGG function for the oracle database versions which are more than 18c or later. We can use the JSON_ARRAYAGG instead of the XMLAGG function in the above query in the following way which generates similar results with a little difference of the square brackets for each group.

SELECT department_id,
replace(JSON_ARRAYAGG(f_name || ' ' || l_name ORDER BY department_id RETURNING CLOB),'"','') AS returning_value
FROM customers
GROUP BY department_id;

The execution of the above query will give the following output along with all the grouped result set according to the departments of the store as shown below –

In XML format, the output of the execution of the above query will be as follows along with all the grouped result set according to the departments of the store –

Conclusion

We can use the XMLAGG function in the oracle database management system for aggregating multiple strings or XML fragments to be represented as a concatenated XML element as a single unit. Mostly, strings are aggregated to generate a comma-separated concatenated string which is the collection of all the minor strings. The XMLAGG function works similar to that of SYS_XMLAGG with some minor differences in formatting. In the versions of oracle higher than 18 c, we can also use the JSON_ARRAYAGG function as an alternative to XMLAGG for aggregating multiple values and representing them in a single row or cell. The ORDER BY and GROUP BY statements are mostly used along with the XMLAGG function to get the grouped concatenated result which is properly ordered.

Recommended Articles

This is a guide to Oracle XMLAGG. Here we discuss How we can use the XMLAGG function in the oracle database management system. You may also have a look at the following articles to learn more –

1. Oracle Tablespace
2. Oracle Synonyms
3. Oracle COALESCE
4. Oracle Analytic Functions