XMLType Data fetch by SQL in jasper report

[brijesh.singh]

 

Is there a any where a SQL query defined in report template can fetch XMLType data from Oracle DB and be treated as XMLData source?

I have a set of tables with XMLtype datafield, I need to define a SQL either in report template or pass that as parameter using JasperReport API only to fetch XMLtype data for report. I don't want to use a separate JDBC connection and jdbc executable statement to fetch XMLType data from table and then feed that data to Jasper report or custom XMLdataType source. 

I am looking if jasper API can help out to pull XMLType data from table and that would be treated as data source for report, in other words, does Jasper supports SQLType data supported in JDBC4 as XML data source for report?

Please assist me, I will appreciate your help.

[oesina]

Hi Brijesh,

Since sqljdbc4.jar supports XML, it should not be a problem to retrieve the field. You could try to use java.io.InputStream data type.

 

What you probably would need then is to resolve your XML structure with some xpath expressions. You could do that either with calculated variables running scriptlets or probably you could create a custom data source, so that you could provide it with SQL and Path expressions as parameters and let it figure things out and return your fields both of usualy data types and XML types.

Hope it helps,

good luck,

Olga

[brijesh.singh]

Thanks, Olga, for your suggestion, I tried following things but that did't work:

1) Created a XmlTypeJdbcQueryExecuter class and factory classes as well that extends JRJdbcDataSource that returns JRXmlDataSource.

public class XmlTypeJdbcQueryExecuter extends JRJdbcQueryExecuter ......

2) Registered a new language sqlXML using these classes.

3) Created a report template using sqlXML language where I define a DB SQL that returns XMLType and a set of fields with XPath expression

3) tryed to execute report, not data displayed on report, bank page got created., not sure JRXmlDataSource got used or not, configured SQL executed properly, How this data would be available to report? I didn't add any PARAMETER_XML_DATA_DOCUMENT at all.

4) I tried to wrap XMLType data in JRXmlDataSource and pass to report fill manager that didn't work either, If I am passing JRXmlDataSource as report data, do I need to specify PARAMETER_XML_DATA_DOCUMENT with Xpath based template or this only needed if feeding data as DOM object.

Not sure how I can debug or where things are wrong.

I appreciate your help and assistance.