aspose file tools*
The moose likes JDBC and the fly likes SQL to XML conversion Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL to XML conversion" Watch "SQL to XML conversion" New topic
Author

SQL to XML conversion

Ulrik Laursen
Greenhorn

Joined: Jan 10, 2005
Posts: 3
Working on a project at the moment...

I'm querying an Oracle DB, and I want to generate XML from my query result.

Basicly im gonna get a very small table back as a result of my query of what is a large DB, but I want the XML to show the paths taken in the DB.

========================================

For example:

QUERY RESULT:

COLUMN1
ANSWER

XML should look like:

<ROOT>
<ELEMENT1>
<ELEMENT2>
.....
....
<COLUMN1>answer</COLUMN>
.....
....
</ELEMENT2>
</ELEMENT1>
</ROOT>

Anyone got any source, suggestions, links that would give me any asistance?

Regards
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30973
    
159

Ulrik,
Welcome to JavaRanch!

It sounds like you want the execution plan and the query result. You can get the execution plan in text format and use XSLT to convert it to XML. I don't know of any tool that merges that with the result, so it would be up to you to stick that in.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1135

Ulrik,
It sounds to me like you only want the execution plan in XML (and not the query results). If that is the case, then I have nothing to add to Jeanne's (excellent, in my opinion) answer.

However, if per chance I have misunderstood you, and you do want the query results in XML format, then perhaps the article SQL in, XML out will be of interest.

Good Luck,
Avi.
Ulrik Laursen
Greenhorn

Joined: Jan 10, 2005
Posts: 3
Thank you very much for your reply.

I'll use your ideas and report back on my success!
Ulrik Laursen
Greenhorn

Joined: Jan 10, 2005
Posts: 3
Jeanne,

When u say execution path, is this contained in the ResultSet object?
How exactly can i get this in text format?

Might (and probably is) very simple, but I am feeling stupid not knowing how to do it!

Ulrik
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30973
    
159

Ulrik,
The query result is in the resultset. The execution plan is obtained through a command line tool in Oracle. Take a look at this asktom document for the exact command.

Since an execution plan is database specific, I don't think you can get it through JDBC. You would have to use the command line or Java's Runtime.exec() to make an operating system call.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1135

Ulrik,
Depending on your version of Oracle (sorry, I couldn't find that information in your posts), you can query the data dictionary for the query execution plan. For example, in Oracle 9i, you have the following views:

V$SQL_PLAN
V$SQL_PLAN_STATISTICS
V$SQL_PLAN_STATISTICS_ALL

You'll probably be able to find more details via Oracle's OTN Web site.

Good Luck,
Avi.
 
jQuery in Action, 2nd edition
 
subject: SQL to XML conversion