• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL to XML conversion

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic