A simple method to generate XML output files with Oracle PL*SQL scripts (.sql)

From: Valentin Welter <valentin.welter_at_t-online.de>
Date: 4 Feb 2003 10:32:17 -0800
Message-ID: <7f0ff6a5.0302041032.16e3bbb3_at_posting.google.com>

You find this item on my homepage
http://valentin.welter.bei.t-online.de
Prerequisites

1.	Oracle Client or Personal Oracle
2.	An ASCII text editor
3.	No kits, just XML, DTD, CSS knowledge.

Steps 
1.	Create the Document Type Definition (DTD, .dtd) file
2.	Create the Cascading Style Sheet (CSS, .css) file
3.	Create the PL*SQL script (.sql).

It is recommended to execute these steps parallel (simultaneously): you begin to develop the PL*SQL script and correspondingly develop the DTD and CSS files.

At the PL*SQL script you have to use the function PUT_LINE of DBMS_OUTPUT Oracle package (DBMS_OUTPUT.PUT_LINE) to generate the XML output file. To do it you have to apply your XML knowledge.

You can divide the PL*SQL script into two anonymous blocks: • The 1st anonymous block generates the header and title of the XML file.
• The 2nd anonymous block writes the selected data from the database.

My example, the xml.sql script, uses the common Oracle Demo database. It creates an "Employees List" retrieving the emp table. I have refined my example by highlighting some important data: • The red font highlights the salaries greater than 2000 and the president's name.
• The blue font highlights the managers' names.

You can use this method both under Windows and Unix.

Launching
There are two possibilities to launch the tool under Windows:

[Quoted] [Quoted] •	Start SQL*Plus tool and enter the command: _at_<path>xml.sql
[Quoted] •	On the MS DOS Window or under Unix enter the command: 

sqlplus <user>/<password>_at_<database> @<path>xml.sql

Have a lot of fun! Received on Tue Feb 04 2003 - 19:32:17 CET