how to use xmlagg function

August 25, 2022

here is the table for reference

CREATE TABLE XX_EMPLOYEES

(

EMP_ID NUMBER NOT NULL,

EMP_FIRST_NAME VARCHAR2(250) NOT NULL,

EMP_MIDDLE_NAME VARCHAR2(250) NOT NULL,

EMP_LAST_NAME VARCHAR2(250) NOT NULL,

Hired_Date DATE NOT NULL,

Country VARCHAR2(250) NOT NULL,

Salary NUMBER NOT NULL

);



INSERT ALL

INTO XX_EMPLOYEES (EMP_ID, EMP_FIRST_NAME, EMP_MIDDLE_NAME, EMP_LAST_NAME, Hired_Date, Country, Salary) VALUES (1,'Tomm','Jef','Adam','01-Jan-2016','JORDAN',1000)

INTO XX_EMPLOYEES (EMP_ID, EMP_FIRST_NAME, EMP_MIDDLE_NAME, EMP_LAST_NAME, Hired_Date, Country, Salary) VALUES (2,'Mohammed','Ahmed','Mahmoud','15-Jul-2009','UAE',900)

INTO XX_EMPLOYEES (EMP_ID, EMP_FIRST_NAME, EMP_MIDDLE_NAME, EMP_LAST_NAME, Hired_Date, Country, Salary) VALUES (4,'Ali','Ahmad','Mahmoud','07-Jul-2000','UK',1200)

INTO XX_EMPLOYEES (EMP_ID, EMP_FIRST_NAME, EMP_MIDDLE_NAME, EMP_LAST_NAME, Hired_Date, Country, Salary) VALUES (10,'Basel','Jamal','Saeed','10-Apr-2001','UAE',1000)



SELECT * FROM dual;

I want to use XMLAGG function to return the employee full information in one line, concatenated by #$#

I have used loop function to do it and RTRIM but I need to use XMLAGG. Is it possible?

Result should be like this:

1,Tomm,Jef,Adam,01-JAN-2016,JORDAN,1000 #$# 2,Mohammed,Ahmed,Mahmoud,15-JUL-2009,UAE,900 #$# 4,Ali,Ahmad,Mahmoud,07-JUL-2000,UK,1200 #$# 10,Basel,Jamal,Saeed,10-APR-2001,UAE,1000

Statement processed.

>Solution :

If the final result doesn’t exceed 4000 characters, listagg is simpler to use:

SQL> WITH
  2     one_emp
  3     AS
  4        (SELECT    emp_id
  5                || ','
  6                || emp_first_name
  7                || ','
  8                || emp_middle_name
  9                || ','
 10                || emp_last_name
 11                || ','
 12                || hired_date
 13                || ','
 14                || country
 15                || ','
 16                || salary AS one_employee
 17           FROM xx_employees)
 18  SELECT LISTAGG (one_employee, '#$#') WITHIN GROUP (ORDER BY NULL) AS result
 19    FROM one_emp;

RESULT
--------------------------------------------------------------------------------
1,Tomm,Jef,Adam,01.01.16,JORDAN,1000#$#10,Basel,Jamal,Saeed,10.04.01,UAE,1000#$#
2,Mohammed,Ahmed,Mahmoud,15.07.09,UAE,900#$#4,Ali,Ahmad,Mahmoud,07.07.00,UK,1200

If the result is longer than 4000 characters (which might be, if there are many employees involved) or you just want to use xmlagg, then

SQL> WITH
  2     one_emp
  3     AS
  4        (SELECT    emp_id
  5                || ','
  6                || emp_first_name
  7                || ','
  8                || emp_middle_name
  9                || ','
 10                || emp_last_name
 11                || ','
 12                || hired_date
 13                || ','
 14                || country
 15                || ','
 16                || salary AS one_employee
 17           FROM xx_employees)
 18  SELECT RTRIM (
 19            XMLAGG (XMLELEMENT (e, one_employee || '#$#') ORDER BY NULL).EXTRACT (
 20               '//text()'),
 21            '#$#') AS result
 22    FROM one_emp;

RESULT
--------------------------------------------------------------------------------
1,Tomm,Jef,Adam,01.01.16,JORDAN,1000#$#2,Mohammed,Ahmed,Mahmoud,15.07.09,UAE,900
#$#4,Ali,Ahmad,Mahmoud,07.07.00,UK,1200#$#10,Basel,Jamal,Saeed,10.04.01,UAE,1000


SQL>

It isn’t too difficult to convert that code into a function:

SQL> CREATE OR REPLACE FUNCTION f_test
  2     RETURN CLOB
  3  IS
  4     retval  CLOB;
  5  BEGIN
  6     WITH
  7        one_emp
  8        AS
  9           (SELECT    emp_id
 10                   || ','
 11                   || emp_first_name
 12                   || ','
 13                   || emp_middle_name
 14                   || ','
 15                   || emp_last_name
 16                   || ','
 17                   || hired_date
 18                   || ','
 19                   || country
 20                   || ','
 21                   || salary AS one_employee
 22              FROM xx_employees)
 23     SELECT LISTAGG (one_employee, '#$#') WITHIN GROUP (ORDER BY NULL)
 24       INTO retval
 25       FROM one_emp;
 26
 27     RETURN retval;
 28  END;
 29  /

Function created.

Let’s try it:

SQL> select f_test from dual;

F_TEST
--------------------------------------------------------------------------------
1,Tomm,Jef,Adam,01.01.16,JORDAN,1000#$#10,Basel,Jamal,Saeed,10.04.01,UAE,1000#$#
2,Mohammed,Ahmed,Mahmoud,15.07.09,UAE,900#$#4,Ali,Ahmad,Mahmoud,07.07.00,UK,1200


SQL>