Home » SQL & PL/SQL » SQL & PL/SQL » Reurning result sets as XML
Reurning result sets as XML [message #243584] Thu, 07 June 2007 16:07 Go to next message
artmt
Messages: 32
Registered: October 2006
Location: Boston
Member
Can someone suggest a good way to return result sets of several unrelated queries as a single XML document?
Re: Reurning result sets as XML [message #243591 is a reply to message #243584] Thu, 07 June 2007 19:47 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://awads.net/wp/2005/12/19/producing-xml-from-sql-using-cursor-expressions/
Re: Reurning result sets as XML [message #243702 is a reply to message #243584] Fri, 08 June 2007 09:07 Go to previous messageGo to next message
artmt
Messages: 32
Registered: October 2006
Location: Boston
Member
I am aware of the cursor expression based solution, but I don't think it applies to my problem.
I am trying to combine several unrelated queries.

-Art
Re: Reurning result sets as XML [message #243703 is a reply to message #243702] Fri, 08 June 2007 09:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put an example and maybe we can answer.

Regards
Michel
Re: Reurning result sets as XML [message #243718 is a reply to message #243702] Fri, 08 June 2007 11:21 Go to previous messageGo to next message
artmt
Messages: 32
Registered: October 2006
Location: Boston
Member
Suppose a single form requests data to populate two picklists - Country and Department.

This data is returned by the following two queries:
select country_code, country_name
from country;

select dept_number, dept_name
from dept;


If I use DBMS_XMLGEN to execute each query, and then append the results - I get two XML documents contatenated together.

Is there a way to format this data as a single document, without having to parse and then re-assemble XML?

Does this make more sense now?

Thanks
-Art
Re: Reurning result sets as XML [message #243728 is a reply to message #243718] Fri, 08 June 2007 12:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe is this you want:
SQL> select dbms_xmlgen.getxml('
  2  select cursor(select empno, ename from emp where rownum <= 3) emps,
  3         cursor(select deptno, dname from dept where rownum <= 3) depts
  4  from dual') xml
  5  from dual
  6  /
XML
----------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPS>
   <EMPS_ROW>
    <EMPNO>7369</EMPNO>
    <ENAME>SMITH</ENAME>
   </EMPS_ROW>
   <EMPS_ROW>
    <EMPNO>7499</EMPNO>
    <ENAME>ALLEN</ENAME>
   </EMPS_ROW>
   <EMPS_ROW>
    <EMPNO>7521</EMPNO>
    <ENAME>WARD</ENAME>
   </EMPS_ROW>
  </EMPS>
  <DEPTS>
   <DEPTS_ROW>
    <DEPTNO>10</DEPTNO>
    <DNAME>ACCOUNTING</DNAME>
   </DEPTS_ROW>
   <DEPTS_ROW>
    <DEPTNO>20</DEPTNO>
    <DNAME>RESEARCH</DNAME>
   </DEPTS_ROW>
   <DEPTS_ROW>
    <DEPTNO>30</DEPTNO>
    <DNAME>SALES</DNAME>
   </DEPTS_ROW>
  </DEPTS>
 </ROW>
</ROWSET>

1 row selected.

Regards
Michel
Re: Reurning result sets as XML [message #243731 is a reply to message #243584] Fri, 08 June 2007 12:16 Go to previous message
artmt
Messages: 32
Registered: October 2006
Location: Boston
Member
This is great.
Thank you Michel.
Previous Topic: Create a folder from Procedure
Next Topic: Help with reading ranges from a different table
Goto Forum:
  


Current Time: Tue Dec 06 10:31:39 CST 2016

Total time taken to generate the page: 0.05142 seconds