Of course! Converting data from a Java SQL result set to XML is a common task. There are several ways to achieve this, ranging from using standard Java libraries to leveraging more powerful frameworks.

Let's break down the most popular methods with code examples.

Scenario

We'll use a simple employees table for all examples.

SQL Table: employees

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);
INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Alice', 'Engineering', 95000.00),
(2, 'Bob', 'Marketing', 75000.50),
(3, 'Charlie', 'Engineering', 110000.00);

Our goal is to convert the result of SELECT * FROM employees; into an XML structure like this:

<?xml version="1.0" encoding="UTF-8"?>
<employees>
  <employee>
    <id>1</id>
    <name>Alice</name>
    <department>Engineering</department>
    <salary>95000.00</salary>
  </employee>
  <employee>
    <id>2</id>
    <name>Bob</name>
    <department>Marketing</department>
    <salary>75000.50</salary>
  </employee>
  <employee>
    <id>3</id>
    <name>Charlie</name>
    <department>Engineering</department>
    <salary>110000.00</salary>
  </employee>
</employees>

Method 1: Using Standard Java (JDBC + Manual String Building)

This approach is lightweight and requires no external libraries. You manually iterate through the ResultSet and build an XML string using a StringBuilder. This is great for simple cases or when you want to avoid dependencies.

How it works:

1. Connect to the database using JDBC.
2. Execute a query and get a ResultSet.
3. Iterate through the ResultSet.
4. For each row, append XML tags and the column values to a StringBuilder.
5. Handle special characters like <, >, and & by escaping them.

Code Example:

import java.sql.*;
public class SqlToXmlManual {
    public static String convertToXml() {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "your_username";
        String password = "your_password";
        StringBuilder xmlBuilder = new StringBuilder();
        // Start the XML document
        xmlBuilder.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
        xmlBuilder.append("<employees>\n");
        String sql = "SELECT id, name, department, salary FROM employees";
        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                xmlBuilder.append("  <employee>\n");
                // Get column names dynamically for a more robust solution
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    String value = rs.getString(i);
                    // Escape special characters in the value
                    String escapedValue = escapeXml(value);
                    xmlBuilder.append(String.format("    <%s>%s</%s>\n", columnName, escapedValue, columnName));
                }
                xmlBuilder.append("  </employee>\n");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null; // Or throw an exception
        }
        // End the XML document
        xmlBuilder.append("</employees>");
        return xmlBuilder.toString();
    }
    private static String escapeXml(String input) {
        if (input == null) {
            return "";
        }
        return input.replace("&", "&amp;")
                   .replace("<", "&lt;")
                   .replace(">", "&gt;")
                   .replace("\"", "&quot;")
                   .replace("'", "&apos;");
    }
    public static void main(String[] args) {
        String xmlOutput = convertToXml();
        if (xmlOutput != null) {
            System.out.println(xmlOutput);
        }
    }
}

Pros:

• No external dependencies.
• Full control over the XML structure.

Cons:

• Verbose and error-prone (e.g., forgetting to escape characters).
• Not efficient for large datasets due to string concatenation.
• Manual mapping can be tedious.

Method 2: Using DOM (Document Object Model)

This is a more structured, standard Java approach. You build an in-memory tree structure (Document) and then serialize it to an XML string.

How it works:

1. Create a new Document using a DocumentBuilder.
2. Create the root element (<employees>).
3. Iterate through the ResultSet.
4. For each row, create a new <employee> element.
5. For each column, create a child element (e.g., <id>) and set its text content.
6. Add the child elements to the <employee> element.
7. Add the <employee> element to the root.
8. Use a Transformer to convert the Document object into an XML string.

Code Example:

import org.w3c.dom.*;
import javax.xml.parsers.*;
import javax.xml.transform.*;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import java.sql.*;
public class SqlToXmlDom {
    public static String convertToXml() {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "your_username";
        String password = "your_password";
        try {
            // 1. Create a Document
            DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();
            DocumentBuilder docBuilder = docFactory.newDocumentBuilder();
            Document doc = docBuilder.newDocument();
            // 2. Create root element
            Element rootElement = doc.createElement("employees");
            doc.appendChild(rootElement);
            String sql = "SELECT id, name, department, salary FROM employees";
            try (Connection conn = DriverManager.getConnection(url, user, password);
                 Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(sql)) {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                // 3. Iterate through ResultSet
                while (rs.next()) {
                    // 4. Create employee element
                    Element employee = doc.createElement("employee");
                    rootElement.appendChild(employee);
                    // 5. Create child elements for each column
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnName(i);
                        String value = rs.getString(i);
                        Element element = doc.createElement(columnName);
                        // The DOM parser handles character escaping automatically
                        element.appendChild(doc.createTextNode(value));
                        employee.appendChild(element);
                    }
                }
            }
            // 6. Convert Document to XML String
            TransformerFactory transformerFactory = TransformerFactory.newInstance();
            Transformer transformer = transformerFactory.newTransformer();
            transformer.setOutputProperty(OutputKeys.INDENT, "yes"); // Pretty print
            transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "2");
            DOMSource source = new DOMSource(doc);
            StreamResult result = new StreamResult(new java.io.StringWriter());
            transformer.transform(source, result);
            return result.getWriter().toString();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
    public static void main(String[] args) {
        String xmlOutput = convertToXml();
        if (xmlOutput != null) {
            System.out.println(xmlOutput);
        }
    }
}

Note: This uses standard Java libraries (javax.xml.parsers, javax.xml.transform), so no external Maven/Gradle dependency is needed for these specific classes.

Pros:

• Structured and less error-prone than manual string building.
• The DOM API automatically handles XML character escaping.
• You can manipulate the XML tree in memory before serializing.

Cons:

• Can be memory-intensive for very large ResultSets because the entire XML tree is held in memory.

Method 3: Using a Library (Jackson or JAXB)

For most real-world applications, using a dedicated JSON/XML library is the best practice. Jackson is a very popular choice in the Java world and can handle this elegantly.

Using Jackson

Jackson can read from a ResultSet almost like it reads from a POJO (Plain Old Java Object). We first define a simple class to represent our data.

Add Jackson Dependency (Maven)

<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.15.2</version> <!-- Use the latest version -->
</dependency>

Create a POJO

// Employee.java
public class Employee {
    private int id;
    private String name;
    private String department;
    private double salary;
    // Getters and Setters (Required by Jackson)
    public int getId() { return id; }
    public void setId(int id) { this.id = id; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    public String getDepartment() { return department; }
    public void setDepartment(String department) { this.department = department; }
    public double getSalary() { return salary; }
    public void setSalary(double salary) { this.salary = salary; }
}

Convert ResultSet to List of POJOs, then to XML

Jackson doesn't have a built-in ResultSet reader, but it's easy to map manually.

import com.fasterxml.jackson.dataformat.xml.XmlMapper;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class SqlToXmlJackson {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "your_username";
        String password = "your_password";
        List<Employee> employees = new ArrayList<>();
        String sql = "SELECT id, name, department, salary FROM employees";
        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                Employee emp = new Employee();
                emp.setId(rs.getInt("id"));
                emp.setName(rs.getString("name"));
                emp.setDepartment(rs.getString("department"));
                emp.setSalary(rs.getDouble("salary"));
                employees.add(emp);
            }
        }
        // Use Jackson to convert the list of objects to XML
        XmlMapper xmlMapper = new XmlMapper();
        // The root name will be "list" by default. We can configure it.
        // For a simple list, this is fine. For a custom root, you'd use wrappers.
        String xml = xmlMapper.writeValueAsString(employees);
        System.out.println(xml);
    }
}

Output of the above code:

<list>
  <Employee>
    <id>1</id>
    <name>Alice</name>
    <department>Engineering</department>
    <salary>95000.0</salary>
  </Employee>
  <Employee>
    <id>2</id>
    <name>Bob</name>
    <department>Marketing</department>
    <salary>75000.5</salary>
  </Employee>
  <Employee>
    <id>3</id>
    <name>Charlie</name>
    <department>Engineering</department>
    <salary>110000.0</salary>
  </Employee>
</list>

Pros:

• Very concise and readable code.
• Declarative mapping (using POJOs).
• Powerful and flexible (handles complex objects, nested structures, etc.).
• Excellent performance.

Cons:

• Requires an external library dependency.

Summary and Recommendation

Recommendation:

• For quick prototypes, homework assignments, or scripts where you can't add dependencies, the Manual String method is acceptable.
• For more robust, standard Java applications where you want to avoid external libraries and the dataset isn't huge, the DOM method is a solid choice.
• For serious, production-level applications, using a library like Jackson is the highly recommended approach. It's cleaner, safer, and more maintainable. The slight overhead of adding a dependency is far outweighed by the benefits.