Home » SQL & PL/SQL » SQL & PL/SQL » XML : Union of 2 Select queries
XML : Union of 2 Select queries [message #574761] Tue, 15 January 2013 11:53 Go to next message
rtutus
Messages: 3
Registered: January 2013
Junior Member
Do you know ho I can get the union result of 2 queries and put them in xml result, but I want each query to be in seperate xml element.
I don t want to put 1 single xmlelement and do a From then construct a virtual table uniting the 2 subqueries

I mean I don t want something like the following:

(Select
XMLAGG(
XMLELEMENT("credit",
XMLForest(
field1 "detail"
)
)
)
FROM
(
--SubQuery1
Select field1 from myTable1
union
--SubQuery1
Select field1 from myTable2
)
) "credits"

I want something like here:

XMLELEMENT("credits",
(SELECT
XMLAGG(
XMLELEMENT("credit",
XMLForest(field1 "field1")
)
)
FROM
myTable1

),
(SELECT
XMLAGG(
XMLELEMENT("credit",
XMLForest(field1 "field1")
)
)
FROM
myTable2
)
)

Except the 2nd alternative is not working Sad
I get error message:
"SQL command not properly ended"

Thanks a lot
Re: XML : Union of 2 Select queries [message #574763 is a reply to message #574761] Tue, 15 January 2013 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: XML : Union of 2 Select queries [message #574769 is a reply to message #574761] Tue, 15 January 2013 13:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Your post is unreadable.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: XML : Union of 2 Select queries [message #574783 is a reply to message #574769] Tue, 15 January 2013 16:24 Go to previous messageGo to next message
rtutus
Messages: 3
Registered: January 2013
Junior Member
Missing From Dual at the end
So query should be like this:

select XMLELEMENT("credits",
(SELECT XMLAGG(XMLELEMENT("credit",
XMLForest(field1 "field1")))
FROM myTable1),
(SELECT XMLAGG(XMLELEMENT("credit",
XMLForest(field1 "field1")))
FROM myTable2))
from dual

Thanks Smile
Re: XML : Union of 2 Select queries [message #574808 is a reply to message #574783] Wed, 16 January 2013 00:18 Go to previous message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 15 January 2013 20:02
Welcome to the forum.

Your post is unreadable.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel

Previous Topic: Facing problem with a Query
Next Topic: Problem with performance
Goto Forum:
  


Current Time: Fri Sep 19 23:14:06 CDT 2014

Total time taken to generate the page: 0.08110 seconds