Oops I am wrong..
"Rangeshwara Reddy Kona" <konareddy_at_hotmail.com> wrote in message
news:3be9e3fd$1_at_news.cadence.com...
> Hi,
> try this
> select '<deptemp>' || chr(10) || '<body>' || '<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>' || '</deptemp>' || chr(10) || '</body>'
> from dept a, emp b
> where a.deptno = b.deptno
> order by a.deptno, empno;
>
> It generated following output in spool
> SQL> @create_xml
>
> '<DEPTEMP>'||CHR(10)||'<BODY>'
> --------------------------------------------------------------------------
--
> ----
> <deptemp>
> <body><emp>
> <deptno>10</deptno>
> <dname>ACCOUNTING</dname>
> <empno>7782</empno>
> <ename>CLARK</ename>
> <sal>2450</sal>
> </emp></deptemp>
> </body>
>
> <deptemp>
>
> '<DEPTEMP>'||CHR(10)||'<BODY>'
> --------------------------------------------------------------------------
--
> ----
> <body><emp>
> <deptno>10</deptno>
> <dname>ACCOUNTING</dname>
> <empno>7839</empno>
> <ename>KING</ename>
> <sal>5000</sal>
> </emp></deptemp>
> </body>
>
> <deptemp>
> <body><emp>
>
> '<DEPTEMP>'||CHR(10)||'<BODY>'
> --------------------------------------------------------------------------
--
> ----
> <deptno>10</deptno>
> <dname>ACCOUNTING</dname>
> <empno>7934</empno>
> <ename>MILLER</ename>
> <sal>1300</sal>
> </emp></deptemp>
> </body>
>
> <deptemp>
> <body><emp>
> <deptno>20</deptno>
>
> '<DEPTEMP>'||CHR(10)||'<BODY>'
> --------------------------------------------------------------------------
--
> ----
> <dname>RESEARCH</dname>
> <empno>7369</empno>
> <ename>SMITH</ename>
>
> <sal>800</sal>
> </emp></deptemp>
> </body>
>
> <deptemp>
> <body><emp>
> <deptno>20</deptno>
> <dname>RESEARCH</dname>
>
> '<DEPTEMP>'||CHR(10)||'<BODY>'
> --------------------------------------------------------------------------
--
> ----
> <empno>7566</empno>
> <ename>JONES</ename>
> <sal>2975</sal>
> </emp></deptemp>
> </body>
>
> <deptemp>
> <body><emp>
> <deptno>20</deptno>
> <dname>RESEARCH</dname>
> <empno>7788</empno>
>
> '<DEPTEMP>'||CHR(10)||'<BODY>'
> --------------------------------------------------------------------------
--
> ----
> <ename>SCOTT</ename>
> <sal>3000</sal>
> </emp></deptemp>
> </body>
>
> <deptemp>
> <body><emp>
> <deptno>20</deptno>
> <dname>RESEARCH</dname>
> <empno>7876</empno>
> <ename>ADAMS</ename>
>
> '<DEPTEMP>'||CHR(10)||'<BODY>'
> --------------------------------------------------------------------------
--
> ----
> <sal>1100</sal>
> </emp></deptemp>
> </body>
>
> <deptemp>
> <body><emp>
> <deptno>20</deptno>
> <dname>RESEARCH</dname>
> <empno>7902</empno>
> <ename>FORD</ename>
> <sal>3000</sal>
>
> '<DEPTEMP>'||CHR(10)||'<BODY>'
> --------------------------------------------------------------------------
--
> ----
> </emp></deptemp>
> </body>
>
> <deptemp>
> <body><emp>
> <deptno>30</deptno>
> <dname>SALES</dname>
> <empno>7499</empno>
> <ename>ALLEN</ename>
> <sal>1600</sal>
> </emp></deptemp>
>
> '<DEPTEMP>'||CHR(10)||'<BODY>'
> --------------------------------------------------------------------------
--
> ----
> </body>
>
> <deptemp>
> <body><emp>
> <deptno>30</deptno>
> <dname>SALES</dname>
> <empno>7521</empno>
> <ename>WARD</ename>
> <sal>1250</sal>
> </emp></deptemp>
> </body>
>
> '<DEPTEMP>'||CHR(10)||'<BODY>'
> --------------------------------------------------------------------------
--
> ----
>
> <deptemp>
> <body><emp>
> <deptno>30</deptno>
> <dname>SALES</dname>
> <empno>7654</empno>
> <ename>MARTIN</ename>
> <sal>1250</sal>
> </emp></deptemp>
> </body>
>
>
> '<DEPTEMP>'||CHR(10)||'<BODY>'
> --------------------------------------------------------------------------
--
> ----
> <deptemp>
> <body><emp>
> <deptno>30</deptno>
> <dname>SALES</dname>
> <empno>7698</empno>
> <ename>BLAKE</ename>
> <sal>2850</sal>
> </emp></deptemp>
> </body>
>
> <deptemp>
>
> '<DEPTEMP>'||CHR(10)||'<BODY>'
> --------------------------------------------------------------------------
--
> ----
> <body><emp>
> <deptno>30</deptno>
> <dname>SALES</dname>
> <empno>7844</empno>
> <ename>TURNER</ename>
> <sal>1500</sal>
> </emp></deptemp>
> </body>
>
> <deptemp>
> <body><emp>
>
> '<DEPTEMP>'||CHR(10)||'<BODY>'
> --------------------------------------------------------------------------
--
> ----
> <deptno>30</deptno>
> <dname>SALES</dname>
> <empno>7900</empno>
> <ename>JAMES</ename>
> <sal>950</sal>
> </emp></deptemp>
> </body>
>
>
> 14 rows selected.
>
> SQL> spool off
>
> "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 Wed Nov 07 2001 - 19:51:32 CST
