Oracle XMLAGG tips

The XMLAGG function takes a list of XML elements from one column and returns an aggregated XML document in a single cell.

% Note: XMLAGG is an aggregate function.

The prototype of the XMLAGG function is shown below,

XMLAGG(<XML_ELEMENT> ORDER BY <VALUE>)

In the below listing, the XMLAGG function aggregates the XML element of the first name of two employees into a single cell XML data.

SELECT xmlagg(xmlforest(First_name) order by first_name) XMLAGG

FROM employees

WHERE employee_id IN (100,101);

Result:

<FIRST_NAME>Neena</FIRST_NAME><FIRST_NAME>Steven</FIRST_NAME>

But this result misses an enclosing parent tag to make it an XML document, which can be formed wrapping this query with the XMLELEMENT function as shown below,

SELECT xmlelement(Names,xmlagg(xmlforest(First_name) order by first_name)) XMLAGG

FROM employees

WHERE employee_id IN (100,101);

Result:

<NAMES><FIRST_NAME>Neena</FIRST_NAME><FIRST_NAME>Steven</FIRST_NAME></NAMES>

Prior to the creation of the LISTAGG function in the version 11gR2, XMLAGG was used for aggregating the column values. The below statement imitates the functioning of the LISTAGG function.

SELECT REPLACE(REPLACE(REPLACE(xmlagg(xmlforest(First_name)

ORDER BY first_name),'</FIRST_NAME><FIRST_NAME>',','),'<FIRST_NAME>'),'</FIRST_NAME>') XMLAGG

FROM employees

WHERE employee_id IN (100,101);

Result:

Neena,Steven

In Oracle 9i and beyond we can use the xmlagg function to aggregate multiple rows onto one column:

select
   deptno,
   rtrim (xmlagg (xmlelement (e, ename || ',')).extract ('//text()'), ',') enames
from
   emp
group by
   deptno
;

    DEPTNO ENAMES
---------- ----------------------------------------
        10 CLARK,MILLER,KING
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

XMLAGG is used to aggregate multiple rows in a single XML document:

SELECT
   XMLELEMENT
   (
      EMP,
      XMLAGG
      (
         XMLELEMENT
         (
            DEPT,
            XMLATTRIBUTES(DEPTNO),
            XMLAGG
            (
               XMLELEMENT
               (
                  NAME,
                  ENAME
               )
               ORDER BY
                  ENAME
            )
         )
         ORDER BY
            DEPTNO
      )
)
FROM
   EMP
GROUP BY
   DEPTNO;

EMP
----------------------------
<EMP>
   <DEPT DEPTNO="10">
      <NAME>CLARK</NAME>
      <NAME>KING</NAME>
      <NAME>MILLER</NAME>
   </DEPT>
   <DEPT DEPTNO="20">
      <NAME>ADAMS</NAME>
      <NAME>FORD</NAME>
      <NAME>JONES</NAME>
      <NAME>SCOTT</NAME>
      <NAME>SMITH</NAME>
   </DEPT>
   <DEPT DEPTNO="30">
      <NAME>ALLEN</NAME>
      <NAME>BLAKE</NAME>
      <NAME>JAMES</NAME>
      <NAME>MARTIN</NAME>
      <NAME>TURNER</NAME>
      <NAME>WARD</NAME>
   </DEPT>
</EMP>

The inner XMLAGG function aggregates the employees in each department and the outer XMLAGG aggregates the department in the whole table. The result is a well structured document that displays all the employees.

100% Practical Training
Hands
On
Training
100%
Job
Gurantee

Our Reviews

5 out of 5 based on 12263 ratings.

Google

Facebook

UrbanPro

Sulekha

Yet5

Justdial

best Oracle SQL Training center in chennai

"I thought I knew Oracle SQL until I took this course. My company sent me here against my will. It was definitely worth and I found out how many things I was doing wrong. Karthik is awesome. but i got a lot inspired by you. I will keep in touch and will always try to learn from you as much as I can. Thanks once again Karthik"