Prakash Rai - 2016-01-26

Jonas and team,

First of all, this is a great tool and Thank you.

Is there a way to produce MULTISET data element as pulled multiple empno, ename per deptno in the eg below?

I use json_ml.xml2json(xmltype(x.result)), x.result is the XML output. I wonder if I can use query to achive that within PLJSON.

--Note -- In this query, _t are record type

SELECT DBMS_XMLGEN.getXML (
DBMS_XMLGEN.newContext(
'SELECT dept_t(deptno,
dname,
cast(MULTISET
(SELECT empno, ename FROM emp e WHERE e.deptno = d.deptno)
AS emplist_t))
AS deptempxml
FROM dept d')) FROM DUAL

<ROWSET> <ROW> <DEPTEMPXML deptno="10"> <DNAME>Sports</DNAME> <EMPLIST> <EMP_T empno="200"> <ENAME>John</ENAME> </EMP_T> <EMP_T empno="300"> <ENAME>Jack</ENAME> </EMP_T> </EMPLIST> </DEPTEMPXML> </ROW> <ROW> <DEPTEMPXML deptno="20"> <DNAME>Accounting</DNAME> <EMPLIST> <EMP_T empno="400"> <ENAME>Mary</ENAME> </EMP_T> <EMP_T empno="500"> <ENAME>Jerry</ENAME> </EMP_T> </EMPLIST> </DEPTEMPXML> </ROW> </ROWSET>