OraFAQ Forum: SQL & PL/SQL » XML : Union of 2 Select queries

Home » SQL & PL/SQL » SQL & PL/SQL » XML : Union of 2 Select queries

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

XML : Union of 2 Select queries [message #574761]

Tue, 15 January 2013 11:53

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

Report message to a moderator

Re: XML : Union of 2 Select queries [message #574763 is a reply to message #574761]

Tue, 15 January 2013 12:01

BlackSwan
Messages: 20242
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/

Report message to a moderator

Re: XML : Union of 2 Select queries [message #574769 is a reply to message #574761]

Tue, 15 January 2013 13:02

Michel Cadot
Messages: 54687
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

Report message to a moderator

Re: XML : Union of 2 Select queries [message #574783 is a reply to message #574769]

Tue, 15 January 2013 16:24

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

Report message to a moderator

Re: XML : Union of 2 Select queries [message #574808 is a reply to message #574783]

Wed, 16 January 2013 00:18

Michel Cadot
Messages: 54687
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 19 02:53:31 CDT 2013

Total time taken to generate the page: 0.24434 seconds