Create XML with SQL queries (jsp,mySQL)

[jonas.donas]

hi ng.

i want to generate a xml-file which is constructed out of several
sql-queries from different tables.

<element1> // insert data from table1
<element2> // insert data from table2 and so forth...

this (maybe straightforwarded?) task should be done within a web
application
realized with the java server pages, i.e. a user enters data into the
different tables and then clicks on "create xml" - the result should be
a result.xml file which will be stored in a temp. directory.

my question now is, which technologies (tools) are suitable to use
here?
sax - dom ? db-access methods are available within java beans.

your feedback/ideas/URLs are welcome, thanX.

jay dee

ps: i know, cocoon is doing this task very good, but i don't want to
use it ;-)

 

[iksrazal]

Does this help?

private static final Document transformStatementToDocument(
Connection con,
int call_centre_id,
String sql_statement) throws
HostedPlatformDAOException {

PreparedStatement ps = null;
ResultSet rs = null;

try {

DocumentBuilderFactory factory =
DocumentBuilderFactory.newInstance();
DocumentBuilder builder =
factory.newDocumentBuilder();
Document doc =
builder.newDocument();
Element results = doc.createElement("Results");
doc.appendChild(results);

initCon(con, call_centre_id);

ps = con.prepareStatement(sql_statement);
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();

while (rs.next()) {
Element row = doc.createElement("Row");
results.appendChild(row);
for (int ii = 1; ii <= colCount; ii++) {
String columnName = rsmd.getColumnName(ii);
Object value = rs.getObject(ii);

Element node =
doc.createElement(columnName);

node.appendChild(doc.createTextNode(value.toString()));
row.appendChild(node);
}
}

return doc;

} catch(Exception ex) {
logger.error("transformStatementToDocument:
"+ex.getMessage(), ex);
throw new
HostedPlatformDAOException(ex.getMessage(), ex);
} finally {
if(ps!=null)
try{ps.close();}catch(Exception ex){}
if(rs!=null)
try{rs.close();}catch(Exception ex){}
}

}

iksrazal
http://www.braziloutsource.com/

 

[Raymond DeCampo]

hi ng.

i want to generate a xml-file which is constructed out of several
sql-queries from different tables.

<element1> // insert data from table1
<element2> // insert data from table2 and so forth...

this (maybe straightforwarded?) task should be done within a web
application
realized with the java server pages, i.e. a user enters data into the
different tables and then clicks on "create xml" - the result should be
a result.xml file which will be stored in a temp. directory.

my question now is, which technologies (tools) are suitable to use
here?
sax - dom ? db-access methods are available within java beans.

your feedback/ideas/URLs are welcome, thanX.

jay dee

ps: i know, cocoon is doing this task very good, but i don't want to
use it ;-)

Some databases offer the option to select the data in XML format
directly from the db (e.g. MS SQL Server).

HTH,
Ray

 

[jonas.donas]

hi all,
thanks for your answers, i'll report my hopefully successful tryouts
asap.

greets,
jonas