Carefull, the 'union' sorts that data.
Brian Dick wrote:
>
> Union the three select statements together.
>
> procedure create_xml...
>
> ( O_RESULT_1 OUT sys.types.CURSOR_TYPE)
> AS
> /* create simple xml to be merged with xsl */
> BEGIN
>
> open o_result_1 for
> select '<deptemp>' || chr(10) || '<body>' from dual
>
> union
>
> select
> '<emp>'|| chr(10) ||
> '<deptno>'||a.deptno||'</deptno>'|| chr(10) ||
> '<dname>'||dname||'</dname>'|| chr(10) ||
> '<empno>'||empno||'</empno>'|| chr(10) ||
> '<ename>'||ename||'</ename>'|| chr(10) ||
> '<sal>'||sal||'</sal>'|| chr(10) ||
> '</emp>'
> from dept a, emp b
> where a.deptno = b.deptno
> order by a.deptno, empno
>
> union
>
> select '</deptemp>' || chr(10) || '</body>' from dual;
>
> END;
>
> "Ken Chesak" <kchesak_at_austin.rr.com> wrote in message
> news:W6lG7.12050$qb.706208_at_typhoon.austin.rr.com...
> > I am using the following procedure to create an XML file. The procedure
> is
> > being called by websphere. Right now there are 3 output parameters, I
> would
> > like to make this just 1 output. How can I combine the 3 selects into 1
> > output.
> >
> > Thanks
> >
> > procedure create_xml...
> >
> > ( O_RESULT_1 OUT sys.types.CURSOR_TYPE,
> > O_RESULT_2 OUT sys.types.CURSOR_TYPE,
> > O_RESULT_3 OUT sys.types.CURSOR_TYPE)
> > AS
> > /* create simple xml to be merged with xsl */
> > BEGIN
> >
> > open o_result_1 for
> > select '<deptemp>' || chr(10) || '<body>' from dual;
> >
> > open o_result_2 for
> > select
> > '<emp>'|| chr(10) ||
> > '<deptno>'||a.deptno||'</deptno>'|| chr(10) ||
> > '<dname>'||dname||'</dname>'|| chr(10) ||
> > '<empno>'||empno||'</empno>'|| chr(10) ||
> > '<ename>'||ename||'</ename>'|| chr(10) ||
> > '<sal>'||sal||'</sal>'|| chr(10) ||
> > '</emp>'
> > from dept a, emp b
> > where a.deptno = b.deptno
> > order by a.deptno, empno;
> >
> >
> > open o_result_3 for
> > select '</deptemp>' || chr(10) || '</body>' from dual;
> >
> > END;
> >
> >
> >
> >
Received on Thu Nov 08 2001 - 12:51:42 CST
