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

Post Your Comment

Enter the code given in the above image :

Enter Your Name :

Enter Your Email Address :

Enter Your Comment :

Comments
Posted By narayan On 27-May-09 05:59:53 PM Hi Sir, I got the above result using WebRowSet.But now I want to insert data from the XML file created into the same table using WebRowSet.How can we do that sir.I tried a lot but not successfull.Using DOM parser,parsing the XML file created,I got the result. So,please help me is there a simple way rather than parsing the XML file,to insert data from xml file into DB. Thnak you Sir, Narayan Bali

Posted By ugur On 31-May-09 04:06:58 PM I want this code thank you really , hellow from Turkey

Posted By nagaraju On 24-May-10 03:37:30 PM sir, iam one of ur students. i hv one dbt... "Can we convert XSD file into WSDL?" then how can we convert? plz help me out.

Comments

Posted By narayan On 27-May-09 05:59:53 PM

Hi Sir,

I got the above result using WebRowSet.But now I want to insert data from the XML file created into the same table using WebRowSet.How can we do that sir.I tried a lot but not successfull.Using DOM parser,parsing the XML file created,I got the result.

So,please help me is there a simple way rather than parsing the XML file,to insert data from xml file into DB.

Thnak you Sir,

Narayan Bali

Posted By ugur On 31-May-09 04:06:58 PM

I want this code thank you really , hellow from Turkey

Posted By nagaraju On 24-May-10 03:37:30 PM

sir,
iam one of ur students.
i hv one dbt...
"Can we convert XSD file into WSDL?"
then how can we convert?
plz help me out.