title: how to group aggregate xml elements inside another parent xml tag in pl sql
body: <p>i have following types of xml data stored in oracle database, 
  Updates</p>
  
  <pre><code>    &lt;employee&gt;
      &lt;id&gt;123&lt;/id&gt;
      &lt;year&gt;2016&lt;/year&gt;
      &lt;month&gt;1&lt;/month&gt;
      &lt;paymstr_salhdnm&gt;BASIC PAY&lt;/paymstr_salhdnm&gt;    
      &lt;paymstr_amount&gt;440000&lt;/paymstr_amount&gt;    
      &lt;paymstr_salhdnm&gt;ASSOCIATION SUBSCRIPTION&lt;/paymstr_salhdnm&gt;
      &lt;paymstr_amount&gt;240&lt;/paymstr_amount&gt;
      &lt;paymstr_salhdnm&gt;TELEPHONE ALLOWANCE&lt;/paymstr_salhdnm&gt;
      &lt;paymstr_amount&gt;800&lt;/paymstr_amount&gt;
      &lt;paymstr_salhdnm&gt;HOUSE RENT DEDUCTION&lt;/paymstr_salhdnm&gt;
      &lt;paymstr_amount&gt;2587.5&lt;/paymstr_amount&gt;
      &lt;paymstr_salhdnm&gt;MEDICAL ALLOWANCE&lt;/paymstr_salhdnm&gt;
      &lt;paymstr_amount&gt;700&lt;/paymstr_amount&gt;
      &lt;paymstr_salhdnm&gt;GAS BILL&lt;/paymstr_salhdnm&gt;
      &lt;paymstr_amount&gt;450&lt;/paymstr_amount&gt;
      &lt;/employee&gt;
  </code></pre>
  
  <p>But i want them to be in following format Updates:</p>
  
  <pre><code>&lt;employee&gt;
   &lt;id&gt;123&lt;/id&gt;
   &lt;year&gt;2016&lt;/year&gt;
   &lt;month&gt;1&lt;/month&gt;
  &lt;head&gt;
  &lt;type&gt;1&lt;/type&gt;
  &lt;paymstr_salhdnm&gt;BASIC PAY&lt;/paymstr_salhdnm&gt;
  &lt;paymstr_amount&gt;440000&lt;/paymstr_amount&gt;
  &lt;/head&gt;
  &lt;head&gt;
  &lt;type&gt;1&lt;/type&gt;
  &lt;paymstr_salhdnm&gt;ASSOCIATION SUBSCRIPTION&lt;/paymstr_salhdnm&gt;
  &lt;paymstr_amount&gt;240&lt;/paymstr_amount&gt;
  &lt;/head&gt;
  &lt;head&gt;
  &lt;type&gt;1&lt;/type&gt;
  &lt;paymstr_salhdnm&gt;TELEPHONE ALLOWANCE&lt;/paymstr_salhdnm&gt;
  &lt;paymstr_amount&gt;800&lt;/paymstr_amount&gt;
  &lt;/head&gt;
  &lt;head&gt;
  &lt;type&gt;1&lt;/type&gt;
  &lt;paymstr_salhdnm&gt;HOUSE RENT DEDUCTION&lt;/paymstr_salhdnm&gt;
  &lt;paymstr_amount&gt;2587.5&lt;/paymstr_amount&gt;
  &lt;/head&gt;
  &lt;head&gt;
  &lt;type&gt;1&lt;/type&gt;
  &lt;paymstr_salhdnm&gt;MEDICAL ALLOWANCE&lt;/paymstr_salhdnm&gt;
  &lt;paymstr_amount&gt;700&lt;/paymstr_amount&gt;
  &lt;/head&gt;
  &lt;head&gt;
  &lt;type&gt;1&lt;/type&gt;
  &lt;paymstr_salhdnm&gt;GAS BILL&lt;/paymstr_salhdnm&gt;
  &lt;paymstr_amount&gt;450&lt;/paymstr_amount&gt;
  &lt;/head&gt;
  &lt;/employee&gt;
  </code></pre>
  
  <p>Here is my pl sql code Updates:</p>
  
  <pre><code>      BEGIN   
     xmlData:=XMLType(master_file_data);
     employee_id := xmlData.extract('/employee_id/text()').getstringval();
     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 TBL_PAYROLL_MASTER_FILE s where  s.PAYMSTR_EMPID = e.PAYMSTR_EMPID and 
                    s.PAYMSTR_SALYR = e.PAYMSTR_SALYR and s.PAYMSTR_SALMT = e.PAYMSTR_SALMT)      
                        )   AS clob into ret
      from  
         ( select distinct PAYMSTR_EMPID, PAYMSTR_SALYR, PAYMSTR_SALMT 
           from  TBL_PAYROLL_MASTER_FILE where  PAYMSTR_EMPID = employee_id ) e;
  
   RETURN '&lt;result&gt;&lt;status &gt;success&lt;/status&gt;'||ret.getClobval()||'&lt;/result&gt;';
  </code></pre>
  
  <p>How can i do this.please help me with simple solution.please let me know for any further information.Thanks</p>