Home » SQL & PL/SQL » SQL & PL/SQL » Need help to build xml tag in oracle sql queries (oracle 10g)
| Need help to build xml tag in oracle sql queries [message #608933] |
Thu, 27 February 2014 04:08  |
 |
spatava
Messages: 12
Registered: April 2012
Location: Pune india
|
Junior Member |
|
|
Hi,
I want to build structure like below:
<Employee>
<Name>ABCD</Name>
<MobileNo>07000000000</MobileNo>
<Address>N</Address>
<Department>
<Dept>
<Salary>1</Salary>
<Technology>2</Technology>
<Exp>1</Exp>
</Dept>
<Dept>
<Salary>1</Salary>
<Technology>2</Technology>
<Exp>1</Exp>
</Dept>
</Department>
</Employee>
Here, Name, MobileNo and Address fields can be extract from employee table and
Dept details like Salary, technology and exp details can be extract from Department table.
Can anyone please help to build this xml structure in sql query.
Thanks,
Suchita
|
|
|
|
|
|
| Re: Need help to build xml tag in oracle sql queries [message #608935 is a reply to message #608933] |
Thu, 27 February 2014 04:29  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Here's an example:
SQL> select xmlelement("Employee",
2 xmlelement("Id", e.empno),
3 xmlelement("Name", e.ename),
4 xmlelement("Salary", e.sal),
5 xmlelement("Department",
6 xmlelement("DeptNo", d.deptno),
7 xmlelement("DeptName", d.dname)
8 )
9 ) res
10 from emp e, dept d
11 where d.deptno = e.deptno
12 and rownum = 1
13 /
RES
-------------------------------------------------------------------------------------------------------------------------------------------------
<Employee><Id>7369</Id><Name>SMITH</Name><Salary>800</Salary><Department><DeptNo>20</DeptNo><DeptName>RESEARCH</DeptName></Department></Employee>
[Updated on: Thu, 27 February 2014 04:30] Report message to a moderator |
|
|
|
|
|
| Re: Need help to build xml tag in oracle sql queries [message #608942 is a reply to message #608940] |
Thu, 27 February 2014 05:08 |
|
spatava
Messages: 12
Registered: April 2012
Location: Pune india
|
Junior Member |
|
|
I tried above query with xmlagg..
select xmlelement("Employee",
xmlagg( xmlelement("Id", e.empno),
xmlelement("Name", e.ename),
xmlelement("Salary", e.sal),
xmlelement("Department",
xmlelement("DeptNo", d.deptno),
xmlelement("DeptName", d.dname)
)
) )res
from emp e, dept d
where d.deptno = e.deptno;
i got below error:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'SYS_IXMLAGG'
Please help !!!
|
|
|
|
| Re: Need help to build xml tag in oracle sql queries [message #608950 is a reply to message #608942] |
Thu, 27 February 2014 07:16 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Here's an example:
SQL> select xmlserialize(
2 document xmlelement("Department",
3 xmlelement("DeptNo", d.deptno),
4 xmlelement("DeptName", d.dname),
5 xmlagg(xmlelement("Employee",xmlforest(e.empno,e.ename,e.sal)))
6 )
7 indent) res
8 from dept d, emp e
9 where e.deptno = d.deptno
10 group by d.deptno, d.dname
11 /
RES
-----------------------------------------------------------------------------------------------
<Department>
<DeptNo>10</DeptNo>
<DeptName>ACCOUNTING</DeptName>
<Employee>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<SAL>2450</SAL>
</Employee>
<Employee>
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
<SAL>1300</SAL>
</Employee>
<Employee>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<SAL>5000</SAL>
</Employee>
</Department>
<Department>
<DeptNo>20</DeptNo>
<DeptName>RESEARCH</DeptName>
<Employee>
<EMPNO>7566</EMPNO>
<ENAME>JONES</ENAME>
<SAL>2975</SAL>
</Employee>
<Employee>
<EMPNO>7788</EMPNO>
<ENAME>SCOTT</ENAME>
<SAL>3000</SAL>
</Employee>
<Employee>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<SAL>800</SAL>
</Employee>
<Employee>
<EMPNO>7876</EMPNO>
<ENAME>ADAMS</ENAME>
<SAL>1100</SAL>
</Employee>
<Employee>
<EMPNO>7902</EMPNO>
<ENAME>FORD</ENAME>
<SAL>3000</SAL>
</Employee>
</Department>
<Department>
<DeptNo>30</DeptNo>
<DeptName>SALES</DeptName>
<Employee>
<EMPNO>7521</EMPNO>
<ENAME>WARD</ENAME>
<SAL>1250</SAL>
</Employee>
<Employee>
<EMPNO>7654</EMPNO>
<ENAME>MARTIN</ENAME>
<SAL>1250</SAL>
</Employee>
<Employee>
<EMPNO>7698</EMPNO>
<ENAME>BLAKE</ENAME>
<SAL>2850</SAL>
</Employee>
<Employee>
<EMPNO>7900</EMPNO>
<ENAME>JAMES</ENAME>
<SAL>950</SAL>
</Employee>
<Employee>
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<SAL>1600</SAL>
</Employee>
<Employee>
<EMPNO>7844</EMPNO>
<ENAME>TURNER</ENAME>
<SAL>1500</SAL>
</Employee>
</Department>
3 rows selected.
[Updated on: Thu, 27 February 2014 07:17] Report message to a moderator |
|
|
|
| Re: Need help to build xml tag in oracle sql queries [message #608951 is a reply to message #608942] |
Thu, 27 February 2014 07:18 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
select xmlserialize(
document xmlelement(
"Employees",
xmlagg(
xmlelement(
"Employee",
xmlelement("Id", e.empno),
xmlelement("Name", e.ename),
xmlelement("Salary", e.sal),
xmlelement(
"Department",
xmlelement("DeptNo", d.deptno),
xmlelement("DeptName", d.dname)
)
)
)
)
indent size = 2
) res
from emp e, dept d
where d.deptno = e.deptno
/
RES
------------------------------------------
<Employees>
<Employee>
<Id>7782</Id>
<Name>CLARK</Name>
<Salary>2450</Salary>
<Department>
<DeptNo>10</DeptNo>
<DeptName>ACCOUNTING</DeptName>
</Department>
</Employee>
<Employee>
RES
------------------------------------------
<Id>7839</Id>
<Name>KING</Name>
<Salary>5000</Salary>
<Department>
<DeptNo>10</DeptNo>
<DeptName>ACCOUNTING</DeptName>
</Department>
</Employee>
<Employee>
<Id>7934</Id>
<Name>MILLER</Name>
RES
------------------------------------------
<Salary>1300</Salary>
<Department>
<DeptNo>10</DeptNo>
<DeptName>ACCOUNTING</DeptName>
</Department>
</Employee>
<Employee>
<Id>7566</Id>
<Name>JONES</Name>
<Salary>2975</Salary>
<Department>
RES
------------------------------------------
<DeptNo>20</DeptNo>
<DeptName>RESEARCH</DeptName>
</Department>
</Employee>
<Employee>
<Id>7902</Id>
<Name>FORD</Name>
<Salary>3000</Salary>
<Department>
<DeptNo>20</DeptNo>
<DeptName>RESEARCH</DeptName>
RES
------------------------------------------
</Department>
</Employee>
<Employee>
<Id>7876</Id>
<Name>ADAMS</Name>
<Salary>1100</Salary>
<Department>
<DeptNo>20</DeptNo>
<DeptName>RESEARCH</DeptName>
</Department>
</Employee>
RES
------------------------------------------
<Employee>
<Id>7369</Id>
<Name>SMITH</Name>
<Salary>800</Salary>
<Department>
<DeptNo>20</DeptNo>
<DeptName>RESEARCH</DeptName>
</Department>
</Employee>
<Employee>
<Id>7788</Id>
RES
------------------------------------------
<Name>SCOTT</Name>
<Salary>3000</Salary>
<Department>
<DeptNo>20</DeptNo>
<DeptName>RESEARCH</DeptName>
</Department>
</Employee>
<Employee>
<Id>7521</Id>
<Name>WARD</Name>
<Salary>1250</Salary>
RES
------------------------------------------
<Department>
<DeptNo>30</DeptNo>
<DeptName>SALES</DeptName>
</Department>
</Employee>
<Employee>
<Id>7844</Id>
<Name>TURNER</Name>
<Salary>1500</Salary>
<Department>
<DeptNo>30</DeptNo>
RES
------------------------------------------
<DeptName>SALES</DeptName>
</Department>
</Employee>
<Employee>
<Id>7499</Id>
<Name>ALLEN</Name>
<Salary>1600</Salary>
<Department>
<DeptNo>30</DeptNo>
<DeptName>SALES</DeptName>
</Department>
RES
------------------------------------------
</Employee>
<Employee>
<Id>7900</Id>
<Name>JAMES</Name>
<Salary>950</Salary>
<Department>
<DeptNo>30</DeptNo>
<DeptName>SALES</DeptName>
</Department>
</Employee>
<Employee>
RES
------------------------------------------
<Id>7698</Id>
<Name>BLAKE</Name>
<Salary>2850</Salary>
<Department>
<DeptNo>30</DeptNo>
<DeptName>SALES</DeptName>
</Department>
</Employee>
<Employee>
<Id>7654</Id>
<Name>MARTIN</Name>
RES
------------------------------------------
<Salary>1250</Salary>
<Department>
<DeptNo>30</DeptNo>
<DeptName>SALES</DeptName>
</Department>
</Employee>
</Employees>
SQL>
SY.
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 19:37:05 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
