Greens Technologys Whatsapp
Greens Technologys Whatsapp

Oracle XMLAGG tips

+91-89399 15577

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


PRAGMA in Oracle PL SQL

Our Reviews 5 Star Rating: Recommended - Best IT Training in Chennai

5  out of 5  based on 12263 ratings.

Google          5 Star Rating: Recommended - Best IT Training in Chennai

Facebook      5 Star Rating: Recommended - Best IT Training in Chennai

UrbanPro       5 Star Rating: Recommended - Best IT Training in Chennai

Sulekha         5 Star Rating: Recommended - Best IT Training in Chennai

Yet5              5 Star Rating: Recommended - Best IT Training in Chennai

Justdial          5 Star Rating: Recommended - Best IT Training in Chennai


Oracle SQL Training in Chennai

Oracle SQL Training in Chennai
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"


Greens Technologys Overall Reviews


Greens Technologys Overall Reviews

5 out of 5 based on 17,981 ratings. 17,981 user reviews.





Relevant Courses