How to get data from XML file that is stored in the database

[mrongen]

I need to create a report containing data from a MS SQL database AND data from within an XML file that is stored in the same database (so it is actually a databas within a database). Is this possible using Jasper 4.7.1 and how can I do this? I have some experience writing SQL queries, but this is really difficult.

My thoughts right now are to export the XML file to a directory, although I do not know how to do that using SQL. From the main report (showing data from the MS SQL database) I need to create a subreport that can handle the XML file.

 

[nicolaifriis]

You can create reports on XML data with XPath queries (I guess you already figured this part out). The tricky part is getting your XML file from the database and use it as a XML datasource.

What I usually do (might be other ways to do this too) is write a SQL query that gets the XML blob from the database and map it to a java.sql.Blob field in the report. (The approach would have to be adjusted a bit if the XML is in another type in the database, I usually keep them as blobs.)

I then create a datasource as an expression for my sub report.

Example: new net.sf.jasperreports.engine.data.JRXmlDataSource($F{xml_blob}.getBinaryStream(), "/settings")

The sub report can then be developed and tested on a local file and run on the server with XML files from the database since it is independant of where the XML file is coming from.

Hope this gives you an idea where to start.

Nicolai