Oracle sql query to xml 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:

&lt;nba dataset="topplayers"&gt;
    &lt;division ID="East"&gt;
        &lt;player&gt;&lt;/player&gt;
                .
                .
                .
        &lt;player&gt;&lt;/player&gt;
&lt;/nba&gt;


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 &lt; 12
            )
          )
        )
       ) AS "nba"
FROM TEAMS T
WHERE T.DIVISION = 'East';
An easy way to convert SQL directly to XML, for example:

SQL&gt; select dbms_xmlgen.getxml('select * from hr.employees where employee_id = 100') 
       as xml from dual;

XML
--------------------------------------------------------------------------------
&lt;?xml version="1.0"?&gt;
&lt;ROWSET&gt;
 &lt;ROW&gt;
  &lt;EMPLOYEE_ID&gt;100&lt;/EMPLOYEE_ID&gt;
  &lt;FIRST_NAME&gt;Steven&lt;/FIRST_NAME&gt;
  &lt;LAST_NAME&gt;King&lt;/LAST_NAME&gt;
  &lt;EMAIL&gt;SKING&lt;/EMAIL&gt;
  &lt;PHONE_NUMBER&gt;515.123.4567&lt;/PHONE_NUMBER&gt;
  &lt;HIRE_DATE&gt;17-JUN-03&lt;/HIRE_DATE&gt;
  &lt;JOB_ID&gt;AD_PRES&lt;/JOB_ID&gt;
  &lt;SALARY&gt;24000&lt;/SALARY&gt;
  &lt;DEPARTMENT_ID&gt;90&lt;/DEPARTMENT_ID&gt;
 &lt;/ROW&gt;
&lt;/ROWSET&gt;


If you have a fixed format of your XML, you can describe it in XSLT and use XMLTRANSFORM to take care of the rest.