Как объединить несколько XMLELEMENT с помощью XMLAGG

Question

Я хочу объединить несколько XMLELEMENT для таблицы после столбцов и вернуть файл XML.

Вот данные таблицы:

Я хочу вернуть 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>

Но я сталкиваюсь с трудностями при агрегировании 2 столбцов [head_name и amount]. вот мой код оракула:

 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;

Как вы можете видеть, поскольку я использовал пустое пространство внутри первого XMLELEMENT XMLAGG, он по-прежнему создает пустые теги со следующим выводом 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>

Как я могу избежать этого лишних пустых тегов и получить свой соответствующий формат XML. Спасибо

Answer 1

Я думаю, что вы должны сосредоточиться на XMLFOREST, это делает свое дело.

Этот запрос предоставляет необходимый 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;

Обратите внимание, что поскольку ваши данные денормализованы, я сначала выбираю отчетливый сотрудник, год и месяц, после чего в подзапросе выбираются все строки сотрудников (для года и месяца).

<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>

Если у вашего сотрудника больше записей (с разными годами и месяцами вы получаете по одной строке XML для каждой из них).

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

Мои тестовые данные

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;