Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL to XML conversion

 
Ulrik Laursen
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34974
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you very much for your reply.

I'll use your ideas and report back on my success!
 
Ulrik Laursen
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34974
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic