How to aggregate multiple XMLELEMENT using XMLAGG

Question

I want to aggregate multiple XMLELEMENT for table following columns and return an xml file.

Here is the table data:

I want return xml with following format:

<employee>
<id>FMCSC00015</id>
<year>2016</year>
<month>1</month>
<head_name>BASIC PAY</head_name>
<amount>35600</amount>
<head_name>BANK LOAN-4</head_name>
<amount>23490</amount>
<head_name>RESEARCH ALLOWANCE</head_name>
<amount>1500</amount>
<head_name>MOTOR GARAGE</head_name>
<amount>500.5</amount>
<head_name>CLUB</head_name>
<amount>207</amount>
.........so on            
<employee>

But i am facing difficulty on aggregating 2 columns [head_name and amount]. here is my oracle code:

 select xmlElement(  "employee",
                      xmlelement("id", e.PAYMSTR_EMPID),
                      xmlelement("year", e.PAYMSTR_SALYR),
                      xmlelement("month", e.PAYMSTR_SALMT),
                      XMLAGG(
                        XMLELEMENT(" ",
                        XMLELEMENT("head_name", e.PAYMSTR_SALHDNM ),
                        XMLELEMENT("amount", e.PAYMSTR_AMOUNT )  ) 
                      )
                  ) as result 
                  from TBL_PAYROLL_MASTER_FILE e                
                  where e.PAYMSTR_EMPID = 'FMCSC00015'
                  group by e.PAYMSTR_EMPID,e.PAYMSTR_SALYR, e.PAYMSTR_SALMT;

As you can see because i used white space inside the first XMLELEMENT of XMLAGG, it still creates empty tags with following output xml:

<employee>
    <id>FMCSC00015</id>
    <year>2016</year>
    <month>1</month>
    <>
    <head_name>BASIC PAY</head_name>
    <amount>35600</amount>
    </>
    <>
    <head_name>BANK LOAN-4</head_name>
    <amount>23490</amount>
    </>
    <>
    <head_name>RESEARCH ALLOWANCE</head_name>
    <amount>1500</amount>
    </>
    <>    
    <head_name>MOTOR GARAGE</head_name>
    <amount>500.5</amount>
    </>
    <>
    <head_name>CLUB</head_name>
    <amount>207</amount>
    .........so on            
    <employee>

How can i avoid this extra empty tags and get my appropriate xml format.Thanks

Answer 1

I thing you should focus on XMLFOREST it makes the trick.

This query provides a required XML.

select   xmlElement(  "employee",
              xmlelement("id", e.PAYMSTR_EMPID),
              xmlelement("year", e.PAYMSTR_SALYR),
              xmlelement("month", e.PAYMSTR_SALMT),
              (select   XMLAGG(  
                 xmlforest(PAYMSTR_SALHDNM, PAYMSTR_AMOUNT  ) )
               from my_tab s where  s.PAYMSTR_EMPID = e.PAYMSTR_EMPID and 
                  s.PAYMSTR_SALYR = e.PAYMSTR_SALYR and s.PAYMSTR_SALMT = e.PAYMSTR_SALMT)      
                      )
from  
   ( select distinct PAYMSTR_EMPID, PAYMSTR_SALYR, PAYMSTR_SALMT 
     from  my_tab where  PAYMSTR_EMPID = 'FMCSC00015' ) e;

Note that because your data are denormalized, I first select distinct the employee, year and month, after that in the subquery all employees rows (for the year and month are fetched).

<employee>
  <id>FMCSC00015</id>
  <year>2016</year>
  <month>1</month>
  <PAYMSTR_SALHDNM>BASIC PAY</PAYMSTR_SALHDNM>
  <PAYMSTR_AMOUNT>35600</PAYMSTR_AMOUNT>
  <PAYMSTR_SALHDNM>BANK LOAN-4</PAYMSTR_SALHDNM>
  <PAYMSTR_AMOUNT>23490</PAYMSTR_AMOUNT>
  <PAYMSTR_SALHDNM>RESEARCH ALLOWANCE</PAYMSTR_SALHDNM>
  <PAYMSTR_AMOUNT>1500</PAYMSTR_AMOUNT>
  <PAYMSTR_SALHDNM>MOTOR GARAGE</PAYMSTR_SALHDNM>
  <PAYMSTR_AMOUNT>500,5</PAYMSTR_AMOUNT>
  <PAYMSTR_SALHDNM>CLUB</PAYMSTR_SALHDNM>
  <PAYMSTR_AMOUNT>207</PAYMSTR_AMOUNT>
</employee>

In case your employee has more records (with different years and months you get one XML row for each of them

<employee>
  <id>FMCSC00015</id>
  <year>2015</year>
  <month>1</month>
  <PAYMSTR_SALHDNM>CLUB</PAYMSTR_SALHDNM>
  <PAYMSTR_AMOUNT>207</PAYMSTR_AMOUNT>
</employee>

My test data

create table my_tab as 
select 'FMCSC00015' PAYMSTR_EMPID, 2016 PAYMSTR_SALYR, 1 PAYMSTR_SALMT, 'BASIC PAY' PAYMSTR_SALHDNM, 35600 PAYMSTR_AMOUNT from dual union all
select 'FMCSC00015' id, 2016 year, 1 month, 'BANK LOAN-4' head_name, 23490 amount from dual union all
select 'FMCSC00015' id, 2016 year, 1 month, 'RESEARCH ALLOWANCE' head_name, 1500 amount from dual union all
select 'FMCSC00015' id, 2016 year, 1 month, 'MOTOR GARAGE' head_name, 500.5 amount from dual union all
select 'FMCSC00015' id, 2016 year, 1 month, 'CLUB' head_name, 207 amount from dual union all
select 'FMCSC00015' id, 2015 year, 1 month, 'CLUB' head_name, 207 amount from dual union all
select 'FMCSC00016' id, 2016 year, 1 month, 'CLUB' head_name, 207 amount from dual;