####Oracle (requires r11.2 or better)
It's easier to break down the query into pieces. You have the following table:
songtitle | artist
final countdown | europe
hello | lionel richie
never gonna give you up | rich ashley
You'd like it to look like this
<music>
<songtitle>hello</songtitle>
<artist>lionel richie</artist>
<songtitle>final countdown</songtitle>
<artist>europe</artist>
<songtitle>never gonna give you up</songtitle>
<artist>rick astley</artist>
</music>
break down the query into xml fragments, it's often easier to work on the aggregated section. so if we get the query working first:
SELECT XMLAGG(
XMLFOREST(songtitle as "songtitle", artist as "artist")) as music
FROM(
SELECT 'hello' AS songtitle, 'lionel richie' AS artist FROM DUAL
UNION ALL
SELECT 'final countdown' AS songtitle, 'europe' AS artist FROM DUAL
UNION ALL
SELECT 'never gonna give you up' AS songtitle, 'rick astley' AS artist FROM DUAL
);
this should get you
<songtitle>hello</songtitle>
<artist>lionel richie</artist>
<songtitle>final countdown</songtitle>
<artist>europe</artist>
<songtitle>never gonna give you up</songtitle>
<artist>rick astley</artist>
now you can proceed to add the root tag
SELECT XMLAGG(XMLELEMENT("music",
(SELECT XMLAGG(
XMLFOREST(songtitle as "songtitle", artist as "artist")) as music
FROM(
SELECT 'hello' AS songtitle, 'lionel richie' AS artist FROM DUAL
UNION ALL
SELECT 'final countdown' AS songtitle, 'europe' AS artist FROM DUAL
UNION ALL
SELECT 'never gonna give you up' AS songtitle, 'rick astley' AS artist FROM DUAL) -- SUBSELECT
) -- SELECT XMLAGG SUBSELECT
) -- XMLELEMENT "music"
) FROM DUAL; -- XMLAGG ROOT
sources