aspose file tools*
The moose likes XML and Related Technologies and the fly likes Generating XML from SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Engineering » XML and Related Technologies
Bookmark "Generating XML from SQL" Watch "Generating XML from SQL" New topic
Author

Generating XML from SQL

Brian Grey
Ranch Hand

Joined: Nov 15, 2002
Posts: 43
I'd like to generate an XML document from a SQL query. Is there a tool that do it easily? How should I do it?

1st step: retrieve the data
....
conn = getConnection();
query = "SELECT * FROM CLIENT";
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
....
2nd step: generate the XML file

I have no idea how to do it. I need your help.
Thanks
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61415
    
  67

Step 3: Profit!

Seriously, you would build up an in-memory DOM using the data you retrieve, then serialize it to the XML file. I am assuming that you are also planning on auto-generating the element names from database and column metadata?

In any case, this is much more of an XML issue than a JDBC one, so I'm going to transfer this over to the XML forum for further discussion.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 861
The FormattedDataSet is good for generating xml.

I have a couple precanned 'templates' that generate xml or you can easily create your own template if the generated xml syntax isn't to your liking. The FormattedDataSet is easy to use and templates are easy to create.

Here is the sample code using one of the 2 default xml templates. Note the clean separation of data and formatting below and the ability to reuse both independently.



For more sample code and a live demo that creates xml go to http://www.fdsapi.com and look at the live demo. The FormattedDataSet is open source, and a nice side benefit is that all code performance metrics (such as how long the queries take and how long it takes to get a connection...) are tracked with JAMon.


http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Brian Grey
Ranch Hand

Joined: Nov 15, 2002
Posts: 43
Thanks a lot!
I'll try it right away.
Brian Grey
Ranch Hand

Joined: Nov 15, 2002
Posts: 43
I got this error while trying to retrieve data:
// 01:30:02,203 INFO [STDOUT] TabularDataFactory error. The following data type does not have a TabularData factory: org.jboss.resource.adapter.jdbc.WrapperDataSource

The project is deployed on JBoss. I use the embeded database; HSQLDB Hypersonic.

What should I do?

FormattedDataSet fds = null;
ResultSetConverter rsc = null;
Context ctx = null;
String xml = null;

try {
ctx = new InitialContext();
dataSource = (javax.sql.DataSource) ctx.lookup("java:/DefaultDS");
fds = new FormattedDataSet();
xml = fds.getFormattedDataSet(dataSource, "SELECT * FROM CLIENT", "xml");
} catch(Exception e) {
System.out.println(e.getMessage());
}
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 861
You should pass the DataSource name, not the DataSource object itself. If you look in my DataAccessJ2EE() class at the following link you'll see that I get the DataSource with the following code. I notice that your jndi naming is different from this though.

I use the following jndi lookup: (DataSource) initialContext.lookup("java:comp/env/jdbc/"+getDataSourceName());

http://www.fdsapi.com/javadocs/com/fdsapi/DataAccessJ2EE.txt



Let me know how that goes. If your jndi lookup is different than mine then you could always inherit from DataAccessJ2EE() and override the getDataSource() method. Once that is done the following should work



The java docs have a good ammount of sample code.
Brian Grey
Ranch Hand

Joined: Nov 15, 2002
Posts: 43
It works!!! Thanks

I had to override the getDataSource() method.

I just had to change this line
(DataSource) initialContext.lookup("java:comp/env/jdbc/"+getDataSourceName());
to
(DataSource) initialContext.lookup("java:/"+getDataSourceName());

Now, it's working well. Thanks a lot for this API.
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 861
I'm glad that worked for you.

Now that you are using the FormattedDataSet you should take advantage of the fact that it comes with JAMon. JAMon is a simple api (start, and stop) that times code. The formattedDataSet is monitored throughout, and in fact unbeknowst to you all the sql you issued has been monitored automatically. Install the JAMonAdmin.jsp into your web app and you can see the stats. In addition you can start monitoring your own code now. JAMon comes with a servlet filter that monitors all page access too.

Also, I gave you the short form answer above. The example does not give the root element of the xml. To do that perform the following. Also templates are easy to create on your own and this xml template was created with a couple lines in a text editior.


 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Generating XML from SQL