ResultSet to XML

[Java Tip]

ResultSet can be converted to XML as shown below:

class JDBCapp  {

  static Connection con;

  public static void main (String args[]) {
    ResultSet rs = null;
    Statement stmt = null;
    String sql;

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

      // connection to an ACCESS MDB
      con = AccessCon.getConnection();

      sql =  "select objet from Email";
      stmt = con.createStatement();
      rs = stmt.executeQuery(sql);

      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);
        }
      }

      System.out.println(getDocumentAsXml(doc));

    }
    catch (Exception e) {
        e.printStackTrace();
    }
    finally {
      try {
        if (con != null) con.close();
        if (stmt != null) stmt.close();
        if (rs != null) rs.close();
      }
      catch (Exception e) {
      }
    }
 }

 public static String getDocumentAsXml(Document doc)
      throws TransformerConfigurationException, TransformerException {
    DOMSource domSource = new DOMSource(doc);
    TransformerFactory tf = TransformerFactory.newInstance();
    Transformer transformer = tf.newTransformer();
    //transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION,"yes");
    transformer.setOutputProperty(OutputKeys.METHOD, "xml");
    transformer.setOutputProperty(OutputKeys.ENCODING,"ISO-8859-1");
    // we want to pretty format the XML output
    // note : this is broken in jdk1.5 beta!
    transformer.setOutputProperty
       ("{http://xml.apache.org/xslt}indent-amount", "4");
    transformer.setOutputProperty(OutputKeys.INDENT, "yes");
    //
    java.io.StringWriter sw = new java.io.StringWriter();
    StreamResult sr = new StreamResult(sw);
    transformer.transform(domSource, sr);
    return sw.toString();
 }
}

class AccessCon {
  public static Connection getConnection() throws Exception {
   Driver d = (Driver)Class.forName
    ("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
   Connection c = DriverManager.getConnection
  ("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=c:/tech97.mdb");
   return c;
   /*
   To use an already defined ODBC Datasource :

     String URL = "jdbc:odbc:myDSN";
     Connection c = DriverManager.getConnection(URL, "user", "pwd");

   */
  }
}