Generating XML Tags Using SQL Query

**********************************************************************************************************


We have a standard oracle package to generate the XML Tags.
We just need to pass the SQL statement as parameter to the package.


Below is an example, which prints Todays date as XML Tag


SELECT   DBMS_XMLGEN.getxml ('select sysdate today_date from dual') xml
  FROM   DUAL;


Below is the output, which you can see by executing above SQL


<?xml version="1.0"?>
<ROWSET>
<ROW>
<TODAY_DATE>21-NOV-11</TODAY_DATE>
</ROW>
</ROWSET>


ROWSET and ROW are the default tags, which gets printed for any sql using this package.
TODAY_DATE is the main tag printed by SQL statement, which we have passed to the package.


**********************************************************************************************************


There is another way to get the same output using some other functions.


Let me start off with a simple query to print TODAY_DATE tag only


SELECT   XMLELEMENT ("TODAY_DATE", SYSDATE) FROM DUAL;


Below is the output for the above statement


<TODAY_DATE>2011-11-21</TODAY_DATE>


Now, Let me change the date format as DD-MON-YY


SELECT   XMLELEMENT ("TODAY_DATE", TO_CHAR (SYSDATE, 'DD-MON-YY')) 
FROM DUAL;


Below is the output for the above statement


<TODAY_DATE>21-NOV-11</TODAY_DATE>


Now, i will modify the query to get same output as we got using standard package


SELECT   '<?xml version="1.0"?>'
         || XMLELEMENT (
               "ROWSET",
               XMLELEMENT (
                  "ROW",
                  XMLELEMENT ("TODAY_DATE", TO_CHAR (SYSDATE, 'DD-MON-YY'))
               )
            )
  FROM   DUAL;


Below is the output for the above statement, which is same as what we got using standard package


<?xml version="1.0"?>
<ROWSET>
<ROW>
<TODAY_DATE>21-NOV-11</TODAY_DATE>
</ROW>
</ROWSET>


**********************************************************************************************************


Let me know if you have any questions. ..


Thanks.. .