Creating XML document from JBDC RowSet

Creating an XML document from a table in the database is one of the common requirements. We will see two ways to do it. First one is to use CachedRowset and manually creating an XML document from scratch, second one is to use WebRowSet to create xml document using writeXml() method.

Creating XML document using CachedRowSet

The following program uses Oracle's implementation of CachedRowSet (OracleCachedRowSet) to retrieve data from JOBS table in Oracle10g and write data into JOBS.XML file.

import java.io.FileWriter;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import oracle.jdbc.rowset.OracleCachedRowSet;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

public class JobsTableToXML {
    public static void main(String[] args) throws Exception {
        // connect to oracle and retrieve data
        OracleCachedRowSet crs = new OracleCachedRowSet();
        crs.setUrl("jdbc:oracle:thin:@localhost:1521:xe");
        crs.setUsername("hr");
        crs.setPassword("hr");
        crs.setCommand("select job_id, job_title from jobs");
        crs.execute();

        // create xml document from scratch
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();

        Document document = builder.newDocument();

        Element jobs, job, ele;
        jobs = (Element) document.createElement("jobs");
        document.appendChild(jobs);

        
        while (crs.next()) {
            // write one job at a time to xml document
            job = (Element) document.createElement("job");
            ele = document.createElement("id");
            ele.appendChild(document.createTextNode(crs.getString("job_id")));
            job.appendChild(ele);

            ele = document.createElement("title");
            ele.appendChild(document.createTextNode(crs.getString("job_title")));
            job.appendChild(ele);

            jobs.appendChild(job);
        }

        // Write XML document from memory to jobs.xml
        
        FileWriter fw = new FileWriter("c:\\jobs.xml");
        TransformerFactory tFactory = TransformerFactory.newInstance();
        Transformer transformer = tFactory.newTransformer();
        DOMSource source = new DOMSource(document);
        StreamResult result = new StreamResult(fw);
        transformer.transform(source, result);
    }
}

Creating XML document using WebRowSet

Creating an XML document becomes much easier with WebRowSet as it provides a method writeXml(), which takes a FileOutputStream or FileWriter and writes the content of WebRowSet to the specified file.

Method writeXml() doesn't write just data, it writes properties and schema of WebRowSet into XML file.

import java.io.FileWriter;
import oracle.jdbc.rowset.OracleWebRowSet;

public class RowSetToXML {
    public static void main(String args[]) throws Exception {
         // get data from JOBS table  
         OracleWebRowSet  wrs = new OracleWebRowSet();
         wrs.setUrl("jdbc:oracle:thin:@localhost:1521:xe");
         wrs.setUsername("hr");
         wrs.setPassword("hr");
         wrs.setCommand("select job_id, job_title from jobs");
         wrs.execute();

         FileWriter fw = new FileWriter("c:\\jobs.xml");
         wrs.writeXml(fw);
     }
}