database - adding manual xml tags in oracle xml query -

database - adding manual xml tags in oracle xml query -

i have written below query in want add together manual xml tags it.

select xmlelement("dept_list", xmlagg ( xmlelement("dept", xmlattributes(d.deptno "deptno"), xmlforest( d.deptno "deptno", d.dname "dname", d.loc "loc", (select xmlagg( xmlelement("emp", xmlforest( e.empno "empno", e.ename "ename", e.job "job", e.mgr "mgr", e.hiredate "hiredate", e.sal "sal", e.comm "comm" ) ) ) emp e e.deptno = d.deptno ) "emp_list" ) ) ) ).extract('*') "depts" dept d d.deptno = 10;

output

<dept_list> <dept deptno="10"> <deptno>10</deptno> <dname>accounting</dname> <loc>new york</loc> <emp_list> <emp> <empno>7934</empno> <ename>miller</ename> <job>clerk</job> <mgr>7782</mgr> <hiredate>1982-01-23</hiredate> <sal>1300</sal> </emp> <emp> <empno>7782</empno> <ename>clark</ename> <job>manager</job> <mgr>7839</mgr> <hiredate>1981-06-09</hiredate> <sal>2450</sal> </emp> <emp> <empno>7839</empno> <ename>king</ename> <job>president</job> <hiredate>1981-11-17</hiredate> <sal>5000</sal> <comm>100</comm> </emp> </emp_list> </dept> </dept_list>

but required output this

**<?xml version="1.0"?> <batch xmlns="urn:mclsoftware.co.uk:hunterii"> <header><count>2</count> <originator>kalpataru</originator> <suppress>y</suppress> </header>** <dept_list> <dept deptno="10"> <deptno>10</deptno> <dname>accounting</dname> <loc>new york</loc> <emp_list> <emp> <empno>7934</empno> <ename>miller</ename> <job>clerk</job> <mgr>7782</mgr> <hiredate>1982-01-23</hiredate> <sal>1300</sal> </emp> <emp> <empno>7782</empno> <ename>clark</ename> <job>manager</job> <mgr>7839</mgr> <hiredate>1981-06-09</hiredate> <sal>2450</sal> </emp> <emp> <empno>7839</empno> <ename>king</ename> <job>president</job> <hiredate>1981-11-17</hiredate> <sal>5000</sal> <comm>100</comm> </emp> </emp_list> </dept> </dept_list> **</batch>**

i using oracle database 10g.

i've found dbmsxml procedures provide upper element (the version tag), not allow add together attributes tags. that, need utilize dbms_xmldom. here's 1 example, can search oracle more finish documentation.

xml database oracle oracle10g