This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes XML and Related Technologies and the fly likes Create XML file by using data in Access Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Engineering » XML and Related Technologies
Bookmark "Create XML file by using data in Access" Watch "Create XML file by using data in Access" New topic
Author

Create XML file by using data in Access

Shilong Zheng
Greenhorn

Joined: May 08, 2004
Posts: 29
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
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
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.


Author of Test Driven (2007) and Effective Unit Testing (2013) [Blog] [HowToAskQuestionsOnJavaRanch]
Shilong Zheng
Greenhorn

Joined: May 08, 2004
Posts: 29
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
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
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
Greenhorn

Joined: May 08, 2004
Posts: 29
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
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
Here's a rough idea how you could do it:
Shilong Zheng
Greenhorn

Joined: May 08, 2004
Posts: 29
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
Greenhorn

Joined: May 08, 2004
Posts: 29
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
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
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
Greenhorn

Joined: May 08, 2004
Posts: 29
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
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
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:
Shilong Zheng
Greenhorn

Joined: May 08, 2004
Posts: 29
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
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
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
Greenhorn

Joined: May 08, 2004
Posts: 29
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
Greenhorn

Joined: May 08, 2004
Posts: 29
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Create XML file by using data in Access
 
Similar Threads
Create XML file by using data in Access
Weird Question
Looking for efficient way to do data delivery.
How to do XML to DB using Message Driven Bean?
Developing GUI through XML