1

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';
2

An easy way to convert SQL directly to XML, for example:

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

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.