convert SQL.ResultSet to XML text
apologies if the question seems trivial., I'm returning to Java after a long stint with Dot.Net.
I'm looking for a generic routine, which takes an Sql.ResultSet and writes a portion of text in XML format.I intend to use such routine at various points in a hierarchy, to create a complete XML.In the first approximation, I assume each row from the resultset will be represented as an element, and column values will be shown as attributes. ( The other alternative would be to represent values as child elements. )
It feels fairly simple to write such a generic routine, but I don't want to reinvent the wheel. Is there some kind of standard API which does the job ? It is worth learning, or shall I rather write 50 lines of source to do this conversion ?I realise most database vendors can now produce XML, but we are about to switch the type of the backend, so I'd rather have this functionality in Java.
Andrew
[941 byte] By [
andrewsca] at [2007-10-2 5:13:20]
/**
* Reads values of a result set and generates a corresponding XML output based on the
* SQL/XML standard. The SQL/XML standard defines the following syntax:
* <pre>
* <TABLENAME1>
*<row>
* <COLNAME1>4711</COLNAME1>
* <COLNAME2>Max Mustermann</COLNAME1>
* <COLNAME3>2003-02-11</COLNAME1>
* <COLNAME4 xsi:nil="true" />
*</row>
*....
* </TABLENAME1>
* </pre>
*
* @param resultSet JDBC result set to read.
* @return SQL/XML representation of the result sets' data.
* @see <a href="http://www.sqlx.org/">H2.3 Task Group</a>
* @see <a href="http://www.acm.org/sigmod/record/issues/0206/standard.pdf">SQL/XML is Making Good Progress</a>
*/
static public String toXML(ResultSet resultSet) {
StringBuilder sb = new StringBuilder(XML.HEADER);
sb.append('\n');
if (resultSet != null) {
try {
ResultSetMetaData rsmd = resultSet.getMetaData();
int columnCount = rsmd.getColumnCount();
if (columnCount > 0) {
String tableName = rsmd.getTableName(1);
StringBuilder rows = new StringBuilder();
StringBuilder cols = new StringBuilder();
while (resultSet.next()) { //for each row
cols = new StringBuilder();
for (int i = 1; i <= columnCount; i++) { //for each column
Object o = resultSet.getObject(i);
if (o != null) {
cols.append("<"+rsmd.getColumnName(i)+">");
cols.append(o);
cols.append("</"+rsmd.getColumnName(i)+">");
} else {
cols.append("<"+rsmd.getColumnName(i)+" xsi:nil=\"true\" />");
}
cols.append('\n');
}
rows.append(XML.encloseInTag("row", cols.toString()));
rows.append('\n'); //line feed after row data
}
sb.append(XML.encloseInTag(tableName, rows.toString()));
sb.append('\n');
}//else: no columns in table!
} catch (SQLException sqle) {
System.out.println("resultSet2String(): "+sqle);
}
}
return sb.toString();
}//toXML()
