XML file generation

by Rustem Sabitov

XML Converter is now available!

Summary

This article describes how to convert a data stored in your Oracle database to an allocated XML file by using the JSP technology. The more useful effect of binding to XML data is to use a JSP page to generate XML file programmatically and bind to that JSP page. The JSP technology provides flexibility in the content that is delivered, as the JSP page can output the data in any format is necessary. We choose way, which binds directly to a manually generated XML file that is output by looping through a recordset and generates XML tags for each record and field of our data source. The purpose of this article is to provide you with an easy-going code that you can try with minimum efforts.

To create a XML file from Oracle database we will use the following steps:
Create DTD document, which describes final XML metadata file
Write HTML entry page
Connect to Oracle Database
Create JSPage, which generates XML file from your database
You can see the block-schema view, which comprises all of the above steps and helps to visualize this strategy:

Choose a data source

We create the XML file from the simple table shown below that we have in our database which metadata we are extracting.
The Cars table contains sample information about the car models:

It is the absolute minimum information we need to maintain in order to make our metadata useful. We will parse this data table record by record in our simple XML metadata server.

Main Features of XML Converter related to Oracle database are:
1. Supports Generation of XML documents from Oracle tables and/or views.
2. Supports in XML Converter GUI inserts, updates, and deletes of database records. These updates are corresponding to a given XML document.
3. Supports general Oracle data types.
4. Create and update Oracle views. By using XML Converter you can create a view in your Oracle database, browse query result, update the view and store it.
5. Supports dynamic generation of Document Type Definition (DTD) while retrieving an XML document from the database.
6. Supports simple transformation of the retrieved XML document using XML Stylesheets (XSL).
Describe metadata by using DTD

First of all, we have to figure out the XML document structure, before we extract metadata into it. For that case, we will use the DTD meta-language. I like the DTD language more than XML Schema (.xdr XML-Data Reduced schema format) because DTD is compact, simple, and understandable. Due to this the DTD is still popular. There is how we are structuring our output XML document here:

<!ELEMENT Cars (Year, Corporation, CarName)>
<!ELEMENT Year (#PCDATA)>
<!ELEMENT Corporation (#PCDATA)>
<!ELEMENT CarName (#PCDATA)>

You can see that a 'Cars' element should have exactly three 'Year', 'Corporation', and 'CarName' children elements. In last three lines we describe these elements. We define a data type for all of them using the #PCDATA (Parsed Character DATA) keyword. It refers to "usual" characters of the document without any markup tags and can be used to explicitly allow or disallow plain text within an element. I guess this DTD code is quite easy.

Main HTML page

We need to create a special entry HTML page that contains the appropriate input boxes. We will insert into this boxes connection information. The HTML page should point to our JSP page.

 

<!-- Main Oracle Connection html page -->

<html><head>

<title>JSP + XML sample - entry page</title>

</head>

<body>

 

<p><strong>Oracle Connection</strong></p>

 

<!-- Include JSPage, which generates XML file from Oracle database -->

<form action="OraDB2XmlFile.jsp" method=get>

 

<table>

       <!-- Insert Oracle Datbase Name -->

       <tr><td>Database:</td><td><input type=text name=database></td></tr>

       <!-- Insert Server Name where your database is stored -->

       <tr><td>Server Name:</td><td><input type=text name=servernm></td></tr>

       <!-- Insert your User Name to connect your database -->

       <tr><td>User Name:</td><td><input type=text name=username></td></tr>

       <!-- Insert your account password -->

       <tr><td>Password:</td><td><input type=password name=password></td></tr>

</table>

 

<br>

<!-- Submit button -->

<input type=submit value="Submit">

</form>

 

</body>

</html>

 

 

This HTML page in your web-browser will look like this:

OraDB2XmlFile JSPage

To create our simple XML metadata server we will use JSPs. Inside this JSP we will create XML processor, which does the following:

Makes a connection to Oracle Database
Identifies a structure of our XML file by using a DTD metadata document
Extracts a metadata from the Oracle database according to the DTD
Generates an XML binary file

To get a database connection, the OraDB2XmlFile.jsp page needs the following information:

Oracle Database URL
Server Name, where your database is stored
Database user name
User's password

Main Oracle Connection html page provides this information as the HTTP request's parameters. There is the resulting JSP listing:

 

<%@ page import="java.sql.*" %>

<%@ page import="java.io.*" %>

 

<%

// Identify a carriage return character for each output line

int iLf = 10;

char cLf = (char)iLf;

 

// Create a new empty binary file, which will content XML output

File outputFile = new File(<YourFileName>);

outputFile.createNewFile();

FileWriter outfile = new FileWriter(outputFile);

// the header for XML file

outfile.write("<?xml version='1.0' encoding='ISO-8859-1'?>"+cLf);

 

try {

       // The HTTP request's parameters to get a database connection

       String servernm = request.getParameter("servernm");

       String database = request.getParameter("database");

       String username = request.getParameter("username");

       String password = request.getParameter("password");

 

       // We access the database directly from the JSP with the Oracle JDBC driver

       DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

       // Define connection string and make a connection to database

       Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@"+servernm+":1521:"+

                                                     database, username, password);

       Statement stat = conn.createStatement();

       // Create a recordset

       ResultSet rset = stat.executeQuery("Select * From Cars");

 

       // Expecting at least one record

       if( !rset.next() ) {

              throw new IllegalArgumentException("No data found for the Cars table");

       }

       outfile.write("<Table>"+cLf);

 

       // Parse our recordset

       while(rset.next()) {

              outfile.write("<Cars>"+cLf);

              outfile.write("<Year>" + rset.getString("Year") +"</Year>"+cLf);

              outfile.write("<Corporation>" + rset.getString("Corporation") +"</Corporation>"+cLf);

              outfile.write("<CarName>" + rset.getString("CarName") +"</CarName>"+cLf);

              outfile.write("</Cars>"+cLf);

       }

       outfile.write("</Table>"+cLf);

       // Everything must be closed

       rset.close();

       stat.close();

       conn.close();

       outfile.close();

}

 

catch( Exception er ) {

%>

       <exception><%= er.getMessage()%></exception>

<%

       outfile.close();

}

%>

This file outputs either the text of a recordset persisted to XML, or the contents of the recordset in straight XML file output that is generated by looping through and adding tags for each field.

Finally we can browse our output XML document by using MS Internet Explorer (version 5 and later). The MS Internet Explorer can be used on the client with XML to show XML in structured format. To make the XML view much friendly for human eyes we should let the browser to transform XML file to HTML format using the rules of an XSL style-sheet. For this purpose, we have to create an additional code file using the Extensible Style Language (XSL), which allows us to specify rules that transform an XML document structure into a formatted tree we can display in the browser. You can see this quite readable XML document.

Resume

These basics are just to introduce the parts of JSP technology and demonstrate how to convert data from Oracle database to XML metadata file by using Java JSP's servlets. But surely the best way to do that is a utilization of the Oracle XSQL Servlet™ in your code.

The XSQL Servlet™ produces dynamic XML documents based on one or more SQL queries. That offers us:
-Optionally transform the resulting XML document in the server or client using XSLT.
-Utilizes Declarative .xsql XML File for Instructions and Transformations.
-Supports SQL statements such as: "Select", "Insert", "Update", & "Delete".
-Built-in and Custom Action Handler Support.
-Programmatic API for Java Applications.

I am coding JSP using XSQL Servlet™… But that's a different story entirely.





Copyright © 2001-2008 RustemSoft