Java DOM XML Parser - printed XML contains one tag instead of multiple ones

Question

I have written some code loading records from MySQL and parsing them and saving into XML file. What I wanted to achieve was somethin like this:

<part><row>.....</row><row>...</row></part>

Now I have:

<part><row>...</row></part>

Here is screen showing my poroblem:

Here is Java code section which parses data into XML Document structure:

private void callSPInParOrWithout(final Document doc, 
            final Connection conn) {
        ResultSet rs = null;
        CallableStatement cs = null;
        try {
            // <part>
            Element part = doc.createElement("part");
            doc.appendChild(part);

            cs = conn.prepareCall("{CALL getBrandRows(?)}");
            cs.setString(1, "Brand#13");

            boolean results = cs.execute();
            while (results) {
                // <row>
                Element row = doc.createElement("row");
                part.appendChild(row);   
                rs = cs.getResultSet();
                while (rs.next()) {
                    // <p_partkey>
                    Element pPartKey = doc.createElement("p_partkey");
                    pPartKey.appendChild(doc.createTextNode(Integer.toString(
                            rs.getInt("p_partkey"))));
                    row.appendChild(pPartKey);
                    // <p_name>
                    Element pName = doc.createElement("p_name");
                    pName.appendChild(doc.createTextNode(rs.getString(
                            "p_name")));
                    row.appendChild(pName);
                    // <p_mfgr>   
                    Element pMfgr = doc.createElement("p_mfgr");
                    pMfgr.appendChild(doc.createTextNode(rs.getString(
                            "p_mfgr")));
                    row.appendChild(pMfgr);
                    // <p_brand>
                    Element pBrand = doc.createElement("p_brand");
                    pBrand.appendChild(doc.createTextNode(rs.getString(
                            "p_brand")));
                    row.appendChild(pBrand);
                    // <p_type>
                    Element pType = doc.createElement("p_type");
                    pType.appendChild(doc.createTextNode(rs.getString(
                            "p_type")));
                    row.appendChild(pType);
                    // <p_size>
                    Element pSize = doc.createElement("p_size");
                    pSize.appendChild(doc.createTextNode(Integer.toString(
                            rs.getInt("p_size"))));
                    row.appendChild(pSize);
                    // <p_container>
                    Element pContainer = doc.createElement("p_container");
                    pContainer.appendChild(doc.createTextNode(rs.getString(
                            "p_container")));
                    row.appendChild(pContainer);
                    // <p_retailprice>
                    Element pRetailPrice = doc.createElement("p_retailprice");
                    pRetailPrice.appendChild(doc.createTextNode(
                            Float.toString(rs.getFloat("p_retailprice"))));
                    // <p_comment>
                    Element pComment = doc.createElement("p_comment");
                    pComment.appendChild(doc.createTextNode(rs.getString(
                            "p_comment")));
                    row.appendChild(pComment);
                }
                results = cs.getMoreResults();
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, e.getMessage(), 
                    "Exception occured", JOptionPane.ERROR_MESSAGE);
        } finally {
            try {
                if (rs != null) rs.close();
                if (cs != null) cs.close();
            } catch (SQLException e) {
            }
        }
    }

As you can see in the screenshot it works well but I made some mistake with creating <row> tag.

Answer 1

You are creating the row element only once, before iterating over all the rows in the ResultSet.

To get the expected result, create the row element inside the while (rs.next()) loop, and move part.appendChild(row); to the end of that loop's body.

CallableStatement#execute() indicates if a result set is available. CallableStatement#getMoreResults() indicates if another result set is available; there is no use in calling it unless your statement returns multiple result sets (see also this answer). If your CallableStatement returns only one result set, you can safely use an if in place of the outer while, and remove the call to getMoreResults().