Create XML file by using data in Access

[Shilong Zheng]

Hi,

I need directions in how to create a XML file by pulling data out from Access and validate it with a DTD text file. Would greatlty appreciate a simple example.

[Lasse Koskela]

You should search our JDBC forum regarding connecting to a Microsoft Access database. Once you've got that part working, you could simply write a SYSTEM DOCTYPE reference in front of the actual XML content manually.

[Shilong Zheng]

Hi,

I manage to pull out the data from the access database using java but i need to use java to access the DTD text file and create a new text file containing the DTD followed by the XML source which i pulled out from the access database.
I am very lost how to go about doing it using java, kindly advise, would appreciate some examples, thanks a million...

Regards
Shilong

[Lasse Koskela]

Could you describe the objects you have the "Access data" in and show an example of the expected output (the DTD + XML thing)?

[Shilong Zheng]

i used

DatabaseMetaData data = conn.getMetaData();
ResultSet rs = data.getColumns(null,null,"Details",null);

to get the column data, there are two string objects and two int objects in the table.

expected output text file

<?xml version = "1.0" encoding = "UTF-8"?>

<!DOCTYPE Table[
<!ELEMENT Table (Details)+>
<!ELEMENT Details(EmployeeName, EmployeeID, Age, Qualification)>
<!ELEMENT EmployeeName (#PCDATA)>
<!ELEMENT EmployeeID (#PCDATA)>
<!ELEMENT Age (#PCDATA)>
<!ELEMENT Qualification (#PCDATA)>
]>

<Table>
<Details>
<EmployeeName>Jerry</EmployeeName>
<EmployeeID>123456</EmployeeID>
<Age>23</Age>
<Qualification>Degree</Qualification>
</Details>
:
:
:

</Table>

Appreciate your help!!!

Regards
Shilong

[Lasse Koskela]

Here's a rough idea how you could do it:

Writer output = new FileWriter(...);
output.write("<?xml version='1.0' encoding='UTF-8'?>");
writeDtd(output); // <!DOCTYPE Table[ ... ]>
ResultSet rs = selectEmployeesStatement.executeQuery();
output.write("<Table>");
while (rs.next()) {
    output.write("<Details>");
    output.write("<EmployeeName>");
    output.write(rs.getString("EMPLOYEE_NAME"));
    output.write("</EmployeeName>");
    output.write("<EmployeeID>");
    output.write(rs.getInt("EMPLOYEE_ID"));
    output.write("</EmployeeID>");
    output.write("<Age>");
    output.write(rs.getInt("AGE"));
    output.write("</Age>");
    output.write("<Qualification>");
    output.write(rs.getString("QUALIFICATION"));
    output.write("</Qualification>");
    output.write("</Details>");
}
rs.close();
output.write("</Table>");
output.close();

[Shilong Zheng]

Hi,

A million thanks to you, it works but the EmployeeID and Age integers show funny characters when i open the XML file on the browser.
If my DTD file is DTD.txt, how do i access the DTD text file and create a new text file?
What does the following statement do?
writeDtd(output); // <!DOCTYPE Table[ ... ]>

How should i rectify the above?


Regards
Shilong

[Shilong Zheng]

Hi,

I manage to solve the funny characters by using rs.getInt to rs.getString ...

I still have not solve this, my DTD file is DTD.txt, how do i access the DTD text file and create a new text file?
What does the following statement do?
writeDtd(output); // <!DOCTYPE Table[ ... ]>

When i process the XML file to produce a HTML file by using the following code, the HTML file does not reflect the data except the table only, kindly advise where i go wrong.

import org.xml.sax.*;
import java.util.*;
import java.io.*;

public class XML extends HandlerBase{

private PrintWriter pw = null;

public XML(String file){
try{
pw = new PrintWriter(new FileWriter("StudentDetails.html"));
}catch(IOException e){
System.out.println("File IO Error:" + e.getMessage());
}

try{
Class loadedClass = Class.forName("com.ibm.xml.parser.SAXDriver");
Parser xParser = (Parser)loadedClass.newInstance();
xParser.setDocumentHandler(this);
xParser.parse(file);

}catch(Exception e){
System.out.println("Error..." + e.getMessage());
}
}

public static void main(String[] args){
XML xmlProcess1 = new XML("StudentDetails.xml");
}

public void error(SAXParseException se){
System.out.println("Error !! Problem with XML" + se.getMessage());
}

public void warning(SAXParseException se){
System.out.println("Warning ! Problem with XML" + se.getMessage());
}

public void startDocument() throws SAXException{
pw.println("<HTML>");
pw.println("<HEAD>");
pw.println("<TITLE>Student Details</TITLE>");
pw.println("</HEAD>");
pw.println("<BODY>");
}

public void startElement(String elementName, AttributeList al) throws SAXException{
if(elementName.equalsIgnoreCase("Table")){
pw.println("<TABLE border='1'>");
pw.println("<TR>");
pw.println("<TD>Details</TD>");
pw.println("<TD>EmployeeID</TD>");
pw.println("<TD>Age</TD>");
pw.println("<TD>Qualification</TD>");
pw.println("</TR>");
}else if(elementName.equalsIgnoreCase("Details")){
pw.println("<TR>");
}else{
pw.print("<TD>");
}
}

public void endElement(String elementName) throws SAXException{
if(elementName.equalsIgnoreCase("Table")){
pw.println("</TABLE>");
}else if(elementName.equalsIgnoreCase("Details")){
pw.println("</TR>");
}else{
pw.println("</TD>");
}
}

public void endDocument() throws SAXException{
pw.println("</BODY>");
pw.println("</HTML>");
pw.close();
}

public void characters(char[] chars, int start, int length)throws SAXException{
String stringRead = new String(chars, start, length);
System.out.println("String read is: " + stringRead );
}
}

Regards
Shilong

[Lasse Koskela]

Originally posted by Shilong Zheng:
I manage to solve the funny characters by using rs.getInt to rs.getString ...

Ok. Apparently you're using character-based data types in your database schema. I misread your post to mean that the two "numeric" columns are physically numeric instead of "just" logically numeric...

Originally posted by Shilong Zheng:
I still have not solve this, my DTD file is DTD.txt, how do i access the DTD text file and create a new text file?
What does the following statement do?
writeDtd(output); // <!DOCTYPE Table[ ... ]>

The writeDtd(output) method call was just an abbreviation for something you'll have to implement yourself -- reading the DTD document from somewhere and writing its contents into the given Writer, 'output'.

Regarding your question about accessing the file, I'd suggest you take a look at some of the I/O examples at JavaAlmanac.com. If you need help after browsing through those code snippets, post a question to our I/O and Streams forum.

Originally posted by Shilong Zheng:
When i process the XML file to produce a HTML file by using the following code, the HTML file does not reflect the data except the table only, kindly advise where i go wrong.

...

public void startElement(String elementName, AttributeList al) throws SAXException{
if(elementName.equalsIgnoreCase("Table")){
pw.println("<TABLE border='1'>");
pw.println("<TR>");
pw.println("<TD>Details</TD>");
pw.println("<TD>EmployeeID</TD>");
pw.println("<TD>Age</TD>");
pw.println("<TD>Qualification</TD>");
pw.println("</TR>");
}else if(elementName.equalsIgnoreCase("Details")){
pw.println("<TR>");
}else{
pw.print("<TD>");
}
}

You've forgotten to implement the "else" branch in your if-else statement...
[ June 27, 2004: Message edited by: Lasse Koskela ]

[Shilong Zheng]

Hi,

else{
pw.print("<TD>");
}

Pardon me for my ignorance, i really have no idea what to write to implement the else branch other than the above given by my friend who claims it works. Would greatly appreciate a simple example to get me going.. thanks!!


Regards
Shilong

[Lasse Koskela]

Originally posted by Shilong Zheng:
else{
pw.print("<TD>");
}

Pardon me for my ignorance, i really have no idea what to write to implement the else branch other than the above given by my friend who claims it works. Would greatly appreciate a simple example to get me going.. thanks!!

The implementation of startElement() you described will only print "<TD>" into the resulting HTML for every <Details> element the parser encounters in the XML document. Instead, you need to print out the values of those elements.

The SAX API works in a way which leads you to code something like the following:

public class MyHandler extends DefaultHandler {
 
    private Details details = null;
    private StringBuffer characterData = new StringBuffer();
 
    // this is called when the parser encounters a starting element tag
    public void startElement(String ns, String qname, String lname, Attributes a) {
        if ("Details".equals(qname)) {
            // create a new Details object for each "Details" record
            details = new Details();
        } else if ("Table".equals(qname)) {
            writer.write("<table>");
        }
    }
 
    // this is called for all "text" content in the XML being parsed
    public void characters(char[] c, int start, int length) {
        characterData.append(c, start, length);
    }
 
    // this is called when the parser encounters a closing element tag
    public void endElement(String ns, String qname, String lname) {
        if ("Details".equals(qname)) {
            // the <Details> element ended, print it out as an HTML table row
            writer.write("<tr><td>");
            writer.write(details.getEmployeeName());
            writer.write("</td><td>");
            writer.write(details.getEmployeeID());
            writer.write("</td><td>");
            writer.write(details.getAge());
            writer.write("</td><td>");
            writer.write(details.getQualification());
            writer.write("</td></tr>");
        } else if ("Age".equals(qname)) {
            details.setAge(characterData.toString());
        } else if ("Qualification".equals(qname)) {
            details.setQualification(characterData.toString());
        } else if (...) {
            ...
        } else if ("Table".equals(qname)) {
            writer.write("</Table>");
        }
 
        // always clear the text buffer when an element ends
        characterData.setLength(0);
    }
}

[Shilong Zheng]

Hi,

I got it, thanks. Another question if you don't mind, how do i retrieve the data in an XML file base on the elements in order to be processed in another class, after which i will display the processed data on the HTML file in the order i wish?? Sample data is as follows, kindly provide some examples for me to work on. Thanks a million!!!

XML file
<?xml version="1.0" standalone="yes" ?>
<!DOCTYPE STUDENTLIST (View Source for full doctype...)>
- <STUDENTLIST>
- <STUDENTMARK>
<STUDENTNAME>Dobbs</STUDENTNAME>
<ASSIGNMENTNO>1</ASSIGNMENTNO>
<MARK>12</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Wilkinson</STUDENTNAME>
<ASSIGNMENTNO>1</ASSIGNMENTNO>
<MARK>28</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Wilkinson</STUDENTNAME>
<ASSIGNMENTNO>2</ASSIGNMENTNO>
<MARK>44</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Roberts</STUDENTNAME>
<ASSIGNMENTNO>1</ASSIGNMENTNO>
<MARK>77</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Roberts</STUDENTNAME>
<ASSIGNMENTNO>2</ASSIGNMENTNO>
<MARK>80</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Roberts</STUDENTNAME>
<ASSIGNMENTNO>3</ASSIGNMENTNO>
<MARK>50</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Wilkinson</STUDENTNAME>
<ASSIGNMENTNO>3</ASSIGNMENTNO>
<MARK>60</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Thomas</STUDENTNAME>
<ASSIGNMENTNO>1</ASSIGNMENTNO>
<MARK>45</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Thomas</STUDENTNAME>
<ASSIGNMENTNO>3</ASSIGNMENTNO>
<MARK>90</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Dobbs</STUDENTNAME>
<ASSIGNMENTNO>2</ASSIGNMENTNO>
<MARK>12</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Thomas</STUDENTNAME>
<ASSIGNMENTNO>2</ASSIGNMENTNO>
<MARK>45</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Dobbs</STUDENTNAME>
<ASSIGNMENTNO>3</ASSIGNMENTNO>
<MARK>12</MARK>
</STUDENTMARK>
</STUDENTLIST>

Display in HTML file

StudentName Assignment 1 Assignment 2 Assignment 3

* the display is in table format
* the student name does not repeat


Regards
Shilong

[Lasse Koskela]

Originally posted by Shilong Zheng:
how do i retrieve the data in an XML file base on the elements in order to be processed in another class, after which i will display the processed data on the HTML file in the order i wish??

1. Parse the XML document into a "java.util.List" full of objects of type "com.shilong.StudentMark" that you've populated based on the XML)
2. Pass the object hierarchy to the other class for processing
3. Sort the list using Collections.sort()
4. Generate HTML based on the sorted list

I don't have the time right now to go into more detail. I'm sure others will chime in if you feed your questions to the forum in smaller pieces

[Shilong Zheng]

Hi,

Can someone advise how do i retrieve the data in an XML file base on the elements in order to be processed in another class? Kindly provide some samples for me to work on, below is my XML file, thanks!!

XML file
<?xml version="1.0" standalone="yes" ?>
<!DOCTYPE STUDENTLIST (View Source for full doctype...)>
- <STUDENTLIST>
- <STUDENTMARK>
<STUDENTNAME>Dobbs</STUDENTNAME>
<ASSIGNMENTNO>1</ASSIGNMENTNO>
<MARK>12</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Wilkinson</STUDENTNAME>
<ASSIGNMENTNO>1</ASSIGNMENTNO>
<MARK>28</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Wilkinson</STUDENTNAME>
<ASSIGNMENTNO>2</ASSIGNMENTNO>
<MARK>44</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Roberts</STUDENTNAME>
<ASSIGNMENTNO>1</ASSIGNMENTNO>
<MARK>77</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Roberts</STUDENTNAME>
<ASSIGNMENTNO>2</ASSIGNMENTNO>
<MARK>80</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Roberts</STUDENTNAME>
<ASSIGNMENTNO>3</ASSIGNMENTNO>
<MARK>50</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Wilkinson</STUDENTNAME>
<ASSIGNMENTNO>3</ASSIGNMENTNO>
<MARK>60</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Thomas</STUDENTNAME>
<ASSIGNMENTNO>1</ASSIGNMENTNO>
<MARK>45</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Thomas</STUDENTNAME>
<ASSIGNMENTNO>3</ASSIGNMENTNO>
<MARK>90</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Dobbs</STUDENTNAME>
<ASSIGNMENTNO>2</ASSIGNMENTNO>
<MARK>12</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Thomas</STUDENTNAME>
<ASSIGNMENTNO>2</ASSIGNMENTNO>
<MARK>45</MARK>
</STUDENTMARK>
- <STUDENTMARK>
<STUDENTNAME>Dobbs</STUDENTNAME>
<ASSIGNMENTNO>3</ASSIGNMENTNO>
<MARK>12</MARK>
</STUDENTMARK>
</STUDENTLIST>


Regards
Shilong

[Shilong Zheng]

Hi,

Can someone show me some examples to do the following two things??

1. Parse the XML document into a "java.util.List" full of objects of type "com.shilong.StudentMark" that you've populated based on the XML)
2. Pass the object hierarchy to the other class for processing

Thanks you!!!

Regards
Shilong