Using XSL Transformations While Querying/Manipulating Relational Data as XML - Employee Management System

Table of Contents

• Objective
• Overview
• User Notes with Screen Shots
• Required Software
• Extracting the Sample
• Database Setup
• Preparing and Running the Sample
• Using the Sample
• Description of Sample Files

The purpose of this sample application is to demonstrate the following:

1) Usage of XSLT while retrieving results of a relational query as XML through ODP.NET (Oracle Data Provider for .NET)

2) Usage of XSLT while Insertion/Update/Deletion of relational data in an Oracle database using XML as input through ODP.NET

With Oracle database version 8.1.7 and later, it is possible to convert data from relational tables into XML. Data in the form of XML can be used for insertion, update and deletion back to relational data in the database. The XML data can be transformed using XML stylesheets (XSL) to display it in different formats like HTML, XML, WML etc. ODP.NET 9.2.0.4 provides this XML support to query and manipulate the relational data using XML with XSL transformations applied on to it . The relational data queried as XML can then be exchanged seamlessly with the Microsoft .NET framework. An XML document can be used to insert, update or delete relational data in the Oracle database with ODP.NET.

ODP.NET 9.2.0.4 provides enhancements to OracleCommand objects like XMLQueryProperties and XMLSaveProperties. XMLQueryProperties and XMLSaveProperties represent the XML properties used by OracleCommand object class when XmlCommandType property is set. For e.g.: OracleCommand.XMLQueryProperties.Xslt represents the stylesheet document used for XML transformation using XSLT.

This sample, an Employee Management System, is a windows application using C#. It uses the 'Scott' schema available with the Oracle database. Some of the XML features provided by this sample include:

• Querying employee data in a different XML format, using XSLT
• Querying employee data in HTML format, viewing using any web browser, using XSLT
• Applying XSL transformation while Inserting a new employee, the XSL changes the XML data into the format that conforms to database.
• Applying XSL transformation while Updating/Deleting employee details, the XSL changes the XML data into the format that conforms to database.

On establishing a successful connection to an Oracle database, the employee data from the 'Emp' table is retrieved and displayed as XML in a textbox and in tabular format in a datagrid. The user is given an option to choose among the two stylesheets available:

• Stylesheet that converts XML data to XML with a different format
• Stylesheet that converts XML data to HTML format that can be viewed in a browser.

Users can update/delete this XML by choosing a stylesheet and submit it to get the corresponding details updated in the database. The user is given the facility to insert a new employee record using an XML document with XSL transformations.

When this sample is run, the user should first enter the appropriate connection details and click the 'Connect' button to connect to the Oracle database. After the database connection is established successfully, the list of employees from the Emp table is displayed in tabular (DataGrid) and XML format in the 'Employee Data Without XSLT' textbox. Choose a stylesheet by clicking on a radiobutton, the chosen stylesheet is applied to the XML data and the transformed result is displayed in 'Employee Data with XSLT' text box, as shown in Figure 1.

(Click on thumbnail to see enlarged view)
Figure 1: Screenshot after XSL transformation being applied on the employees data

To insert a new record into the 'Emp' table using an XML document, click on the 'Create New Record' button. A sample XML record based on the stylesheet chosen, appears in the 'Employee Data with XSLT' textbox, which can be modified to conform the required data within the appropriate tags, as shown in Figure 2. Please refer to the hints displayed in the 'Hints to Execute the Sample' text on the application screen for the instructions to perform the current operation.

(Click on thumbnail to see enlarged view)
Figure 2: Displays sample XML record for insertion

To save the new record click on the 'Insert' button. The new data is reflected in the DataGrid and the 'Employee Data Without XSLT' textbox.

To update the existing records, the user can make changes to the XML document displayed in the 'Employee Data with XSLT' text area. Refer to the 'Hints to Execute the Sample' section for more details. The user can click on the 'Update' button to update data in the database.  The update is then reflected in the Datagrid and the 'Employee Data Without XSLT' text box. The user can also update individual records by clicking on a record in the datagrid. The corresponding XML (with transformation) of the selected record is displayed in the textbox. The user can modify the XML and click on the 'Update' button to update the record in the database.

To delete record(s), the user can select a record from the datagrid and click on the 'Delete' button. A conformation dialog is displayed, as shown in figure 3, the changed data, with all records is reflected in the datagrid and the XML document. Users can also delete all these records in a batch by using the 'Delete' button.

(Click on thumbnail to see enlarged view)
Figure 3: Screen displayed while doing the delete operation

• Visual C# .NET installed with Microsoft Visual Studio .NET 2002 or higher.

• Connectivity to an Oracle Database 8.1.7 or higher, running SQL*Net TCP/IP listener

• Oracle Data Provider for .NET (ODP.NET) 9.2.0.4

• Oracle's XML Developer's Kit for PL/SQL

Unzip XSLTSample.zip using Winzip or command utility to your convenient directory referred to as <Extract_Dir>. Extraction of the zip file results in the creation of "XSLTSample" directory.  Refer Description of Sample Files section for more details.     

No special database setup is required to run this sample. This sample uses 'Scott' sample schema available with the Oracle databases. It uses 'Emp' and 'Dept' tables. In case you do not have the 'Scott' schema, you can alternatively connect to a different database user and create the required tables by running the demobld.sql script available at <Extract_Dir>\XSLTSample\setup at SQL*plus prompt:

SQL> @<Extract_Dir>\XSLTSample\setup\demobld.sql

<Extract_Dir> - is the directory where you have extracted the XSLTSample.zip file.

1. Ensure completion of the Database setup.

2. Open XSLTSample.sln using Visual Studio .NET from <Extract_Dir>\XSLTSample\src folder. 

3. Ensure that the following .NET Component References have been added to XSLTSample project: 
          System, 
          System.Data, 
          System.Drawing, 
          System.Windows.Forms, 
          System.XML,
          Oracle.DataAccess. 

To add the above .NET Components 
a)Go to Menu->View-> Solution Explorer
b)Right click on XSLTSample project, choose 'Add Reference'
c)Choose the above .NET Components from the list displayed
d)Click 'Select', then OK
e) The chosen .NET component gets added to the project

5. Build and run the sample by pressing Ctrl+F5. Refer to Using the Sample section on how to use this sample.

1. When running this sample, enter the connection details and provide the username and password for the user that has the required Emp and Dept tables. Enter the host string as appropriate to your database setup.

2. After providing the connection details, click on the 'Connect' button. In case the connection could not be established successfully, the connection status is displayed as 'Not Connected' in red color. On successful connection, the connection status is displayed as 'Connected' in green color, the DataGrid and 'Employee Data Without XSLT' are populated with employee data and the radio buttons to choose the stylesheet are enabled.

3. The user can choose a stylesheet by selecting a radiobutton. Once the stylesheet is chosen, the chosen stylesheet is applied to the XML and is displayed in the 'Employee Data With XSLT' text area. Also various controls get enabled. The user gets options of inserting/updating/deleting/querying employees data with XSL transformations. The text of the selected stylesheet can be viewed using the 'View Stylesheet' button. It only displays the stylesheet applied during the query operation. In case the user selects and applies the 'XML To HTML Stylesheet', then the user can view the transformed XML output in a web browser by clicking on the 'View Result With Browser' button.The hints provided at the bottom of the user-interface screen help the user to execute the sample application. The following steps provides you with a walk-through these various options mentioned in this step.

4. To insert a new record, the user can click on the 'Create New Record' button. On clicking this button, the 'Employee Data With XSLT' text area is populated with a sample XML record in the format of the selected stylesheet. The user can edit this new record. Do not change 'empno' as it is a key column that is generated automatically by the application as a unique value. The user can view the list of valid 'deptno' from the list. Refer to the hints given. After editing the sample record, the user can click on the 'Insert' button to save the data in the database. On successful insertion of the Emp record, a message indicating this is displayed. If any error occurs, the error message is displayed to the user. Also the data in the Employees Datagrid, 'Employee Data Without XSLT' , 'Employee Data With XSLT' text area is refreshed.

5.To update the existing Emp record the user can click on the row in the datagrid to be updated, the user can edit the data displayed in the 'Employee Data With XSLT'. To change the value of deptno the user can check the value from the list of valid deptno. Do not modify the empno as it is a key column. Also, refer to the hints given on the screen. Then, click on the 'Update' button to update the Emp data in the database. On a successful update, a message indicating this is displayed. If any error occurs, the error message is displayed to the user. Also the data in the Employees Datagrid, 'Employee Data Without XSLT' , 'Employee Data With XSLT' text area is refreshed.

6. To delete a record, the user can click on the row in the datagrid that is to be deleted. The corresponding data is displayed in the 'Employee Data With XSLT' text box. The user can then click 'Delete' button. A message dialog appears with Yes/No option. On selecting 'Yes' the record(s) gets deleted and a confirmation message is displayed. On selecting 'No', no delete happens. On a successful delete, a message indicating this is displayed. If any error occurs, the error message is displayed to the user. Also the data in the Employees Datagrid, 'Employee Data Without XSLT' , 'Employee Data With XSLT' text area is refreshed.

Important Note: This sample uses predefined stylesheets. While modifying the text of the 'Employee Data with XSLT' in step 5, 6, 7, the user needs to be careful, only the values for the 'Emp' tables columns data can be changed. Changing the XML tags or other text may throw an XML parsing exception.

7. To view all records with XSL transformation at any given point while executing the application, 'View All Records' button can be clicked.

8. To change the connection, the user can click on the 'Disconnect' button and connect to the other database by providing relevant connection details.

9. To exit the application, click on the 'Exit' button.

Description of Sample Files

Back To Top

Following is the directory structure of the XSLTSample.zip :

Please enter your comments on this sample in the OTN Sample Code Discussion Forum.