title: Removing XML tag in oracle
body: <pre><code>create or replace
  PROCEDURE GETJOBDETAILS_SP
  (
  i_ID IN number ;
  ) 
  AS
   l_refcursor SYS_REFCURSOR;
  
     qryCtx DBMS_XMLGEN.ctxHandle;
     o_Clob CLOB;
     o_xmltype xmltype;
  
  
  BEGIN
  
   OPEN l_refcursor FOR SELECT XMLROOT(
           XMLELEMENT("dept_list",
             XMLAGG (
               XMLELEMENT("dept",
                 XMLATTRIBUTES(d.deptno AS "deptno"),
                 XMLFOREST(
                   d.deptno AS "deptno",
                   d.dname AS "dname",
                   d.loc AS "loc",
                   (SELECT XMLAGG(
                             XMLELEMENT("emp",
                               XMLFOREST(
                                 e.empno AS "empno",
                                 e.ename AS "ename",
                                 e.job AS "job",
                                 e.mgr AS "mgr",
                                 e.hiredate AS "hiredate",
                                 e.sal AS "sal",
                                 e.comm AS "comm"
                               )
                             )
                           )
                    FROM   emp e
                    WHERE  e.deptno = d.deptno
                   ) "emp_list"
                 )
               )
             )
           )
         ) AS "depts"
  FROM   dept d
  WHERE  d.deptno = i_ID;
  
  
  
   qryCtx := DBMS_XMLGEN.newContext(l_refcursor);
  
   DBMS_XMLGEN.setRowSetTag(qryCtx,null);
   DBMS_XMLGEN.SetRowTag (qryCtx, null );
   DBMS_XMLGEN.setNullHandling( qryCtx, DBMS_XMLGEN.EMPTY_TAG );
  
    o_Clob := DBMS_XMLGEN.getXML( qryCtx );        
    o_xmltype := xmltype(o_Clob);
  
      DBMS_OUTPUT.PUT_LINE(o_xmltype.getClobVal) ;
      DBMS_XMLGEN.closeContext( qryCtx );
  
  END GETJOBDETAILS_SP;
  </code></pre>
  
  <p>Hello All,</p>
  
  <p>I have two table one is 'emp' and second one is 'dept' I have created procedure for getting xml data as shown above.</p>
  
  <p>which giving output like below,</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="US-ASCII"?&gt;
  &lt;depts&gt;
  &lt;dept_list&gt;
    &lt;dept deptno="10"&gt;
      &lt;deptno&gt;10&lt;/deptno&gt;
      &lt;dname&gt;ACCOUNTING&lt;/dname&gt;
      &lt;loc&gt;NEW YORK&lt;/loc&gt;
      &lt;emp_list&gt;
        &lt;emp&gt;
          &lt;empno&gt;7782&lt;/empno&gt;
          &lt;ename&gt;CLARK&lt;/ename&gt;
          &lt;job&gt;MANAGER&lt;/job&gt;
          &lt;mgr&gt;7839&lt;/mgr&gt;
          &lt;hiredate&gt;1981-06-09&lt;/hiredate&gt;
          &lt;sal&gt;2450&lt;/sal&gt;
        &lt;/emp&gt;
        &lt;emp&gt;
          &lt;empno&gt;7839&lt;/empno&gt;
          &lt;ename&gt;KING&lt;/ename&gt;
          &lt;job&gt;PRESIDENT&lt;/job&gt;
          &lt;hiredate&gt;1981-11-17&lt;/hiredate&gt;
          &lt;sal&gt;5000&lt;/sal&gt;
        &lt;/emp&gt;
        &lt;emp&gt;
          &lt;empno&gt;7934&lt;/empno&gt;
          &lt;ename&gt;MILLER&lt;/ename&gt;
          &lt;job&gt;CLERK&lt;/job&gt;
          &lt;mgr&gt;7782&lt;/mgr&gt;
          &lt;hiredate&gt;1982-01-23&lt;/hiredate&gt;
          &lt;sal&gt;1300&lt;/sal&gt;
        &lt;/emp&gt;
      &lt;/emp_list&gt;
    &lt;/dept&gt;
  &lt;/dept_list&gt;
  &lt;depts&gt;
  </code></pre>
  
  <p>but in this xml I dont need the root <code>&lt;depts&gt;</code>
  also when I tried simple select statement of procedure I did not got that <code>&lt;depts&gt;</code> why so ? </p>
  
  <p>I just need output like below without that root ,</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="US-ASCII"?&gt;
  &lt;dept_list&gt;
    &lt;dept deptno="10"&gt;
      &lt;deptno&gt;10&lt;/deptno&gt;
      &lt;dname&gt;ACCOUNTING&lt;/dname&gt;
      &lt;loc&gt;NEW YORK&lt;/loc&gt;
      &lt;emp_list&gt;
        &lt;emp&gt;
          &lt;empno&gt;7782&lt;/empno&gt;
          &lt;ename&gt;CLARK&lt;/ename&gt;
          &lt;job&gt;MANAGER&lt;/job&gt;
          &lt;mgr&gt;7839&lt;/mgr&gt;
          &lt;hiredate&gt;1981-06-09&lt;/hiredate&gt;
          &lt;sal&gt;2450&lt;/sal&gt;
        &lt;/emp&gt;
        &lt;emp&gt;
          &lt;empno&gt;7839&lt;/empno&gt;
          &lt;ename&gt;KING&lt;/ename&gt;
          &lt;job&gt;PRESIDENT&lt;/job&gt;
          &lt;hiredate&gt;1981-11-17&lt;/hiredate&gt;
          &lt;sal&gt;5000&lt;/sal&gt;
        &lt;/emp&gt;
        &lt;emp&gt;
          &lt;empno&gt;7934&lt;/empno&gt;
          &lt;ename&gt;MILLER&lt;/ename&gt;
          &lt;job&gt;CLERK&lt;/job&gt;
          &lt;mgr&gt;7782&lt;/mgr&gt;
          &lt;hiredate&gt;1982-01-23&lt;/hiredate&gt;
          &lt;sal&gt;1300&lt;/sal&gt;
        &lt;/emp&gt;
      &lt;/emp_list&gt;
    &lt;/dept&gt;
  &lt;/dept_list&gt;
  </code></pre>
  
  <p>Please help...!
  Thanks.</p>