Covert database table result into XML - How to generate an XML document

Question

I have a query that when run returns a resultset with the following data in the order and grouping shown:

Country     Region      Town
---------------------------------------
England     North       NewCastle
England     North       Manchester
England     North       Leeds
England     South       London
England     South       Bristol
England     South       Birmingham
England     South       Portsmouth
Norway      North       Trondheim
Norway      North       Tromso
Norway      South       Oslo
Norway      South       Stavanger
Norway      West        Bergen

Using Java, i would like to convert the returned result into an XML document as shown below:

<countries>
    <country>
        <countryName>England</countryName>
        <region name = "south">
            <town>London</town>
            <town>Bristol</town>
            <town>Birmingham</town>
            <town>Portsmouth</town>
        </region>
        <region name = "north">
            <town>NewCastle</town>
            <town>Leeds</town>
        </region>
    <country>
        <country>
        <countryName>Norway</countryName>
        <region name = "south">
            <town>Oslo</town>
            <town>Stavanger</town>
        </region>
        <region name = "west">
            <town>Bergen</town>
        </region>
        <region name = "North">
            <town>Trondheim</town>
            <town>Tromso</town>
        </region>       
    <country>
<countries>

What is the best way to traverse the data so that the tags are created and closed at the correct position? I have seen an example here http://www.mkyong.com/java/how-to-create-xml-file-in-java-jdom-parser/ but the structure of the data is flat unlike the sample i am using which will probably require multiple loops.

I'd rather try to get the query result into Java objects and then serialize them into XML using e.g. XStream. — Dzik
– Dzik, Commented Jun 9, 2012 at 9:46
What do you mean by getting the result into Java objects? Do you mean something like JaxB? — ziggy
– ziggy, Commented Jun 9, 2012 at 9:51
Yes, first I would try to get the query result into entities and then serialize them using JAXB. It's nice and easy of cource if this is an option in your case. — Dzik
– Dzik, Commented Jun 9, 2012 at 9:54
No Jaxb is not really an option for now but we are considering it. — ziggy
– ziggy, Commented Jun 9, 2012 at 10:06
XStream makes it easy to tweak object sets into custom XML with converters. — Dzik
– Dzik, Commented Jun 9, 2012 at 10:18

Stanley Mungai · Accepted Answer · 2019-04-05 03:25:44Z

check this code once by changing attributes mentioned at particular location

    package com.annexure.main;

    import java.io.File;
    import java.io.FileWriter;
    import java.nio.file.FileAlreadyExistsException;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.Statement;

    import org.w3c.dom.Document;
    import org.w3c.dom.Element;

    import com.sun.org.apache.xerces.internal.dom.DocumentImpl;
    import com.sun.org.apache.xml.internal.serialize.OutputFormat;
    import com.sun.org.apache.xml.internal.serialize.XMLSerializer;
    /* JDBC Classes*/
    /* Java IO */
    /* W3C Interfaces */
    /* Xerces DOM Classes */
    /* Xerces Serializer */


    public class XmlMain { 

      public static final String JDBCURL = "oracle.jdbc.driver.OracleDriver"; 
      public static final String JDBCDRIVER ="jdbc:oracle:thin:@localhost:1521:xe"; 
      public static final String SQL = "select empid, empname, role from employee"; 
      public static String OUTPUTFILE = "D:employee.xml"; 
//replace file with Country.xml

      public static void main(String[] args) { 

        try{ 

        /** Step 1 : Making a JDBC Connection with database" **/ 
        Class.forName(JDBCURL) ;
        Connection conn = DriverManager.getConnection(JDBCDRIVER,"system","root"); 

        /** Step 2 : Retrieve the customer data from database **/ 
        Statement statement = conn.createStatement(); 
        ResultSet employeeRS = statement.executeQuery(SQL); 

        /** Step 3 : Build customer XML DOM **/ 
        Document xmlDoc = buildEmployeeXML(employeeRS);

        /** Step 4 : Write output to a file **/ 
        File outputFile = new File(OUTPUTFILE); 
        printDOM(xmlDoc, outputFile); 

        conn.close(); /*Connection close*/ 
        } catch(FileAlreadyExistsException f){
            System.out.println("file alread present at this location");
        }
        catch(Exception e) 
        { 
          System.out.println("Really poor exception handling " +e.toString()); 
        }
      }//Main 

      /*Build XML DOcument from database. The XML object is returned to main method where it is written to flat file.*/ 
      private static Document buildEmployeeXML(ResultSet _employeeRS) throws Exception 
      { 

      Document xmlDoc = new DocumentImpl(); 

      /* Creating the root element */ 
//replace employeetable with countries to set a countries tag
      Element rootElement = xmlDoc.createElement("EmployeeTable"); 
      xmlDoc.appendChild(rootElement); 

      while(_employeeRS.next()) 
       { 

        Element emp = xmlDoc.createElement("employee");
//replace employee with country for country tag

        /* Build the CustomerId as a Attribute*/ 
        emp.setAttribute("empid", _employeeRS.getString("empid")); 

        /* Creating elements within customer DOM*/ 
        Element empName = xmlDoc.createElement("empname"); 
        Element role = xmlDoc.createElement("role"); 

        /* Populating Customer DOM with Data*/ 
        empName.appendChild(xmlDoc.createTextNode(_employeeRS.getString("empname"))); 
        role.appendChild(xmlDoc.createTextNode(_employeeRS.getString("role"))); 

        /* Adding the empname and role elements to the employee Element*/ 
        emp.appendChild(empName); 
        emp.appendChild(role); 

        /* Appending emp to the Root Class*/ 
        rootElement.appendChild(emp); 
       } 
      return xmlDoc; 
      } 

      /* printDOM will write the contents of xml document passed onto it out to a file*/ 
      private static void printDOM(Document _xmlDoc, File _outputFile) throws Exception 
      { 
        OutputFormat outputFormat = new OutputFormat("XML","UTF-8",true); 
        FileWriter fileWriter = new FileWriter(_outputFile); 

        XMLSerializer xmlSerializer = new XMLSerializer(fileWriter, outputFormat); 

        xmlSerializer.asDOMSerializer(); 

        xmlSerializer.serialize(_xmlDoc.getDocumentElement()); 
      } 

     }

UVM · Accepted Answer · 2012-06-09 10:44:04Z

0

If you do not want to parse xml, then construct XML dynamically using string buffer.For ex:

Decide the root not, and iterate over your result set

StringBuffer sb = new StringBuffer();
for(int i =0 ; i<size of your result set; i++)
sb.append("<CountryNamString>"+s.getString(0)+"<regionName>"+rs.getString(1)+"<townName>"+rs.getString(2)+"</townName>");

This is just an example.You can improve this.

answered Jun 9, 2012 at 10:44

UVM

9,9347 gold badges44 silver badges66 bronze badges

Collectives™ on Stack Overflow

Covert database table result into XML - How to generate an XML document

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related