quentinosse
New Member
after going through the oracle documentation about generating xml data from the database, I managed to get this select returning exactly what I want:\[code\]SELECT XMLFOREST ( PARENT_T(d.id, CAST ( MULTISET (SELECT c.id, c.name FROM child c WHERE c.parent_id = p.id) AS CHILDREN_T ) ) AS "Parent" ) FROM parent p;\[/code\]Based on my types:\[code\]create or replace TYPE CHILD_T AS OBJECT ("@ID" VARCHAR2(20), "@NAME" VARCHAR2(20));create or replace TYPE CHILDREN_T AS TABLE OF CHILD_T;create or replace TYPE PARENT_T AS OBJECT ("@ID" VARCHAR2(20), CHILDREN CHILDREN_T );\[/code\]That gets me this:\[code\]<Parent ID="1"> <CHILDREN> <CHILD_T ID="1" NAME="xxxxx" /> <CHILD_T ID="2" NAME="yyyyy" /> <CHILD_T ID="3" NAME="zzzzz" /> </CHILDREN></Parent>\[/code\]That's perfect, but how do I make this exposed as Web Service like:\[code\] http://localhost:8080/orawsv/MY_USER/GET_CHILDREN?wsdl\[/code\]I tried this function:\[code\]FUNCTION GET_CHILDREN ( PARENT_ID IN VARCHAR2) RETURN CLOBAS L_RESULT CLOB;BEGIN SELECT to_clob(XMLFOREST ( PARENT_T (p.id, CAST ( MULTISET (SELECT c.id, c.name FROM child c WHERE c.parentId = p.id) AS CHILDREN_T ) ) AS "ParentObj" )) AS MY_XML INTO L_RESULT FROM parent p WHERE p.i = PARENT_ID; RETURN (L_RESULT);END GET_CHILDREN;\[/code\]But all I get is a: "'Element' is an invalid XmlNodeType " error.What return value should I use? Varchar2 throws an out of buffer error.