Converting a Table in Oracle Database into an XML file

Question

Hi,

I need to create XML file, given a table/view in the Database. The actual requirement would be: The code has to get the table/view name as input parameter, read the schema of the same, create a corresponding XML file with the same structure as that of the table/view, and load the XML with the data of the table/view. Language preferred here is JSP. Kindly let me know how to go about this idea.

Thanks in advance, Geetha

Answer 1

This sort of does what you want, except it isn't specific to Oracle. It produces an XML string, not an XML document.

Excerpt:

private String ExecQueryGetXml(java.sql.PreparedStatement stmt, String rootEltName, String rowEltName) {

  String result= "<none/>";
  String item;
  java.sql.ResultSet resultSet;
  java.sql.ResultSetMetaData metaData ;
  StringBuffer buf = new StringBuffer();
  int i;

  try {
    resultSet = stmt.executeQuery();
    metaData= resultSet.getMetaData();

    int numberOfColumns =  metaData.getColumnCount();
    String[] columnNames = new String[numberOfColumns];
    for( i = 0; i < numberOfColumns; i++) 
      columnNames[i] = metaData.getColumnLabel(i+1);

    try {
      if ((rootEltName!=null) && (!rootEltName.equals("")))
          buf.append('<').append(rootEltName).append('>').append('\n');

      // each row is an element, each field a sub-element
      while ( resultSet.next() ) {
        // open the row elt
        buf.append(' ').append('<').append(rowEltName).append(">\n");
        for( i= 0; i < numberOfColumns; i++) {
          item = resultSet.getString(i+1); 
          if(item==null)   continue;
          buf.append("  <").append(columnNames[i]).append('>');
          // check for CDATA required here? 
          buf.append(item);
          buf.append("</").append(columnNames[i]).append(">\n");
        }
        buf.append("\n </").append(rowEltName).append(">\n");
      }
      if ((rootEltName!=null) && (!rootEltName.equals("")))
          buf.append("</").append(rootEltName).append(">\n");
      result= buf.toString();
    } 
    catch(Exception e1) {
      System.err.print("\n\n----Exception (2): failed converting ResultSet to xml.\n");
      System.err.print(e1);
      result= "<error><message>Exception (2): " + e1.toString() + ". Failed converting ResultSet to xml.</message></error>\n";
    }
  }
  catch(Exception e2) {
    System.err.print("\n\n----Exception (3).\n");
    System.err.print("\n\n----query failed, or getMetaData failed.\n");
    System.err.print("\n\n---- Exc as string: \n" + e2);
    System.err.print("\n\n---- Exc via helper: \n" + 
                     ExceptionHelper.getStackTraceAsString(e2));

    result= "<error><message>Exception (3): " + e2 + ". query failed, or getMetaData() failed.</message></error>\n";
  }

  return result;
}