hi all
i'm trying to make an enquiry from 2 tables with XML function, but i have some problems:
this is sql i'm using for this sample
select from the 2 different tables i'm using
select deptno,dname from dept;
select empno,ename,deptno from emp;
then i go to make the xml
SELECT XMLElement("Department",
dept_t(deptno,
edept,
cast(MULTISET
(SELECT empno, ename
FROM emp e
WHERE e.deptno = e.deptno)
AS emplist_t)))
AS deptxml
FROM dept d
WHERE d.deptno = 10;and i have a result
ORA-00902: tipo di dati non valido
00902. 00000 - "invalid datatype"
*Cause:
*Action:
Errore alla riga: 11, colonna: 34
except this error this is the kind of xml i'm trying to retrieve
<Department>
<DEPT_T DEPTNO="10">
<DNAME>ACCOUNTING</DNAME>
<EMPLIST>
<EMP_T EMPNO="7782">
<ENAME>CLARK</ENAME>
</EMP_T>
<EMP_T EMPNO="7839">
<ENAME>KING</ENAME>
</EMP_T>
<EMP_T EMPNO="7934">
<ENAME>MILLER</ENAME>
</EMP_T>
</EMPLIST>
</DEPT_T>
<DEPT_T DEPTNO="20">
<DNAME>ACCOUNTING2</DNAME>
<EMPLIST>
<EMP_T EMPNO="77821">
<ENAME>CLARK2</ENAME>
</EMP_T>
<EMP_T EMPNO="78391">
<ENAME>KING2</ENAME>
</EMP_T>
<EMP_T EMPNO="79341">
<ENAME>MILLER2</ENAME>
</EMP_T>
</EMPLIST>
</DEPT_T>
</Department>but this is the only way to make a tree xml from more than one select?
thanks
Mauro
I'm not sure why you need the subquery? You can join the two tables together and generate the XML that way.
Group by the columns that will appear at the department level and XMLAgg the employee information together:
set long 10000
with depts as (
select xmlelement (
"dept_t", xmlattributes (d.deptno),
xmlelement (
"emplist", xmlagg (
xmlelement (
"emp_t", xmlattributes (e.empno),
xmlelement ( "ename", e.ename )
)
)
)
) dept
from scott.dept d
join scott.emp e
on e.deptno = d.deptno
where d.deptno in (10, 20)
group by d.deptno
)
select xmlserialize ( --for formatting the output
content xmlelement (
"department",
xmlagg ( dept )
) as varchar2(1000)
indent size = 2
) doc
from depts;
DOC
------------------------------------
<department>
<dept_t DEPTNO="10">
<emplist>
<emp_t EMPNO="7782">
<ename>CLARK</ename>
</emp_t>
<emp_t EMPNO="7934">
<ename>MILLER</ename>
</emp_t>
<emp_t EMPNO="7839">
<ename>KING</ename>
</emp_t>
</emplist>
</dept_t>
<dept_t DEPTNO="20">
<emplist>
<emp_t EMPNO="7369">
<ename>SMITH</ename>
</emp_t>
<emp_t EMPNO="7902">
<ename>FORD</ename>
</emp_t>
<emp_t EMPNO="7876">
<ename>ADAMS</ename>
</emp_t>
<emp_t EMPNO="7788">
<ename>SCOTT</ename>
</emp_t>
<emp_t EMPNO="7566">
<ename>JONES</ename>
</emp_t>
</emplist>
</dept_t>
</department>