From XML to a JDBC ResultSet?
So I'm working with an API that accepts a JDBC ResultSet and I'm wondering what the best approach to my problem would be.
I have a database with some columns in it, one of which contains some XML in a CLOB. I would like to grab this XML, parse it, and push the resulting data to this API.
My question is, what would the best approach be for getting this XML data into a ResultSet? Is it possible to create a ResultSet and fabricate my own columns/rows, adding in the XML data? Or somehow extract the data from the XML via JDBC? I'm relatively new to JDBC so thank you for bearing with me if my questions turn out to be rather trivial!
[656 byte] By [
Wilcoa] at [2007-10-3 4:24:22]
Haha, no kidding.
At the moment though, it seems I'm stuck with it. Its part of Crystal Reports (the RAS API) and I'm using it to push the contents of some XML data to the related reports.
The problem is, Crystal doesn't natively support XML parsing (at least when it's sitting in a database field somewhere), so I have to sandwitch some Java in between and jump through a few hoops to get the **** thing to cooperate.
Anyway, here are my thoughts so far:
1) Use JDBC and query the Oracle database.
2) Grab the XML from the field of one of the rows.
3) Take the resulting data and somehow create a ResultSet containing it.
- OR -
3) Somehow use JDBC to query this XML and then use the resulting ResultSet.
Any thoughts?
Wilcoa at 2007-7-14 22:26:48 >
I actually asked their support staff about that one, and at least with the current version I'm SOL.
I forgot to mention one alternative I have is to convert the XML into an object (which is what it was generated from) and then grab the needed data from it. Though I'm still pretty much back where I started, needing to somehow get this data into a ResultSset.
Wilcoa at 2007-7-14 22:26:48 >
Well it seems to me that the ideal at this point again is a stored proc which returns a result set out of the XML. And I say this because it would decouple that messyness out of your code.
To implement that way you'll need an Oracle person which I am not.
Otherwise grab yourself a XML parser and get to work I guess. A basic result set implementation won't be too hard. I wonder if a RowSet implementation would help you...
