I have the below query.
SELECT DISTINCT P.PLAYERID, P.FIRSTNAME, P.LASTNAME, P.POSITION,PT.POINTS, PT.MINUTES, CALC_TENDEX(P.PLAYERID, PT.TEAMID, 2009) TENDEX, T.DIVISION, PT.TEAMID, T.TEAMNAME
FROM PLAYERS P
JOIN PLAYERS_TEAMS PT ON P.PLAYERID = PT.PLAYERID
JOIN TEAMS T ON PT.TEAMID=T.TEAMID
WHERE PT.YEAR=2009 AND T.DIVISION='East'
ORDER BY TENDEX DESC
FETCH FIRST 12 ROWS ONLY;
I want to output this query as xml, with custom format like this:
<nba dataset="topplayers">
<division ID="East">
<player></player>
.
.
.
<player></player>
</nba>
I have tried using SQL/XML but whatever I try does not return answers I want. Is there some easy way to directly convert it to xml?
EDIT: SQL/XML I have tried (along with various alterations of this)
SELECT DISTINCT XMLELEMENT("nba",
XMLAGG (
XMLELEMENT("division",
XMLATTRIBUTES(T.DIVISION AS "ID"),
(SELECT DISTINCT XMLAGG(
XMLELEMENT("player",
XMLFOREST(
P.PLAYERID AS "playerid",
P.FIRSTNAME AS "firstname",
P.LASTNAME AS "lastname",
P.POSITION AS "position",
PT.POINTS AS "points",
PT.MINUTES AS "minutes",
CALC_TENDEX(PT.PLAYERID, PT.TEAMID, 2009) AS "tindex",
T.DIVISION AS "division",
PT.TEAMID AS "teamid",
T.TEAMNAME AS "teamname"
)
)
)
FROM PLAYERS P
JOIN PLAYERS_TEAMS PT ON P.PLAYERID = PT.PLAYERID
JOIN TEAMS T ON PT.TEAMID=T.TEAMID
WHERE PT.YEAR=2009 AND T.DIVISION='East' AND ROWNUM < 12
)
)
)
) AS "nba"
FROM TEAMS T
WHERE T.DIVISION = 'East';
Best Answer
An easy way to convert SQL directly to XML, for example:
If you have a fixed format of your XML, you can describe it in XSLT and use XMLTRANSFORM to take care of the rest.