title: How to aggregate multiple XMLELEMENT using XMLAGG
body: <p>I want to aggregate multiple XMLELEMENT for table following columns and return an xml file.</p>
  
  <p>Here is the table data:
  <a href="https://i.stack.imgur.com/eO4f1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/eO4f1.png" alt="enter image description here"></a></p>
  
  <p>I want return xml with following format:</p>
  
  <pre><code>&lt;employee&gt;
  &lt;id&gt;FMCSC00015&lt;/id&gt;
  &lt;year&gt;2016&lt;/year&gt;
  &lt;month&gt;1&lt;/month&gt;
  &lt;head_name&gt;BASIC PAY&lt;/head_name&gt;
  &lt;amount&gt;35600&lt;/amount&gt;
  &lt;head_name&gt;BANK LOAN-4&lt;/head_name&gt;
  &lt;amount&gt;23490&lt;/amount&gt;
  &lt;head_name&gt;RESEARCH ALLOWANCE&lt;/head_name&gt;
  &lt;amount&gt;1500&lt;/amount&gt;
  &lt;head_name&gt;MOTOR GARAGE&lt;/head_name&gt;
  &lt;amount&gt;500.5&lt;/amount&gt;
  &lt;head_name&gt;CLUB&lt;/head_name&gt;
  &lt;amount&gt;207&lt;/amount&gt;
  .........so on            
  &lt;employee&gt;
  </code></pre>
  
  <p>But i am facing difficulty on aggregating 2 columns [head_name and amount].
  here is my oracle code:</p>
  
  <pre><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;
  </code></pre>
  
  <p>As you can see because i used white space inside the first XMLELEMENT of XMLAGG, it still creates <strong>empty tags</strong> with following output xml:</p>
  
  <pre><code>&lt;employee&gt;
      &lt;id&gt;FMCSC00015&lt;/id&gt;
      &lt;year&gt;2016&lt;/year&gt;
      &lt;month&gt;1&lt;/month&gt;
      &lt;&gt;
      &lt;head_name&gt;BASIC PAY&lt;/head_name&gt;
      &lt;amount&gt;35600&lt;/amount&gt;
      &lt;/&gt;
      &lt;&gt;
      &lt;head_name&gt;BANK LOAN-4&lt;/head_name&gt;
      &lt;amount&gt;23490&lt;/amount&gt;
      &lt;/&gt;
      &lt;&gt;
      &lt;head_name&gt;RESEARCH ALLOWANCE&lt;/head_name&gt;
      &lt;amount&gt;1500&lt;/amount&gt;
      &lt;/&gt;
      &lt;&gt;    
      &lt;head_name&gt;MOTOR GARAGE&lt;/head_name&gt;
      &lt;amount&gt;500.5&lt;/amount&gt;
      &lt;/&gt;
      &lt;&gt;
      &lt;head_name&gt;CLUB&lt;/head_name&gt;
      &lt;amount&gt;207&lt;/amount&gt;
      .........so on            
      &lt;employee&gt;
  </code></pre>
  
  <p>How can i avoid this extra empty tags and get my appropriate xml format.Thanks</p>