Help in fixing the hierarchical xml generation code with missing end t

[whitesox99]

Hi,

I have the following sample data for my tree menu in a database table. It's just a sample data and the menu hierarchy can go x-levels i.e. 10 to 15 levels deep. I am having issues with the </menu> tag if the structure goes deep. Need this code to be generic regardless of the depth of the menus. Any ideas how to handle this in my code.

MENUID       MENUNAME       PARENTID
10           File1
60           File2          10
70           File3          10
80           File4          10
90           File5          70
100          File6          70
110          File7          90
105          File8          110
106          File9          110
301          View2          300
300          View1             
200          Open

*Need to generate the following output file:*

<?xml version = '1.0' encoding = 'windows-1252'?>
<menus>
  <menu name="10" id="1">
    <menu name="60" id="1"/>
    <menu name="70" id="1">
      <menu name="100" id="1"/>
      <menu name="90" id="1">
        <menu name="110" id="1">
          <menu name="105" id="1"/>
          <menu name="106" id="1"/>
        </menu>
      </menu>
    </menu>
    <menu name="80" id="1"/>
  </menu>
  <menu name="200" id="1"/>
  <menu name="300" id="1">
    <menu name="300" id="1"/>
  </menu>
</menus>

Following is the output generated when running the SQL used in the code below:

MENUID       MENUNAME       PARENTID       PATH
10           File1                         /10
60           File2          10             /10/60
70           File3          10             /10/70
80           File4          10             /10/80
90           File5          70             /10/70/90
100          File6          70             /10/70/100
110          File7          90             /10/70/90/110
105          File8          110            /10/70/90/110/105
106          File9          110            /10/70/90/110/106
301          View2          300            /300/301
300          View1                         /301            
200          Open                          /200

But I am having issues with closing *</menu>* tag if the hierarchy is x-level deep can't figure out how to handle this. Here is the java code I have
so far:

package com.xml.parser.util ;

import java.sql.* ;

import java.util.ArrayList ;
import java.util.StringTokenizer ;

public class BuildXml
{
    public BuildXml ()
    {
    }

    public static void main ( String[] args )
    {
        Connection connection = null ;
        PreparedStatement pstmt = null ;
        ResultSet rset = null ;
        try
        {
            BuildXml buildXml = new BuildXml () ;
            String DBurl = "jdbc:oracle:thin:@localhost:1521:XE" ;
            Class.forName ( "oracle.jdbc.driver.OracleDriver" ) ;
            connection = DriverManager.getConnection ( DBurl , "TESTUSR" , "testa34" ) ;
            String sql = "select * from (select menutbl.*, SYS_CONNECT_BY_PATH(MenuId, '/') as path from TESTUSR.menutbl start with parentid is null connect by NOCYCLE PRIOR MenuId = parentid) order by path" ;
            pstmt = connection.prepareStatement ( sql ) ;
            rset = pstmt.executeQuery () ;

            String header = "<?xml version = '1.0' encoding = 'windows-1252'?><menus>" ;
            String footer = "</menus>" ;
            String body = "" ;
            String name = "" ;
            String path = "" ;
            String id = "" ;
            String parent = "" ;

            ArrayList arrayList = null ;
            ArrayList arrayList2 = null ;
            ArrayList arrayList3 = null ;
            boolean flag3 = false ;

            while ( rset.next () )
            {
                id = rset.getString ( 1 ) ;
                name = rset.getString ( 2 ) ;
                path = rset.getString ( 4 ) ;
                parent = rset.getString ( 3 ) ;

                if ( parent == null )
                {
                    if ( arrayList != null && arrayList.size () > 0 && arrayList2.size () > 0 )
                    {
                        if ( flag3 )
                        {
                            body += "<menu name=\"" + ( String ) arrayList.get ( arrayList2.size () - 1 ) + "\" id=\"1\"/></menu>" ;
                        }
                        else
                        {
                            body += "<menu name=\"" + ( String ) arrayList.get ( arrayList2.size () - 1 ) + "\" id=\"1\"/>" ;
                        }
                    }
                    
                    flag3 = false ;

                    arrayList = new ArrayList () ;
                    arrayList2 = new ArrayList () ;
                    arrayList3 = new ArrayList () ;
                }

                arrayList.add ( id ) ;
                arrayList2.add ( parent ) ;
                arrayList3.add ( id ) ;

                if ( parent != null )
                {
                    flag3 = true ;
                    String p = ( String ) arrayList.get ( arrayList2.size () - 2 ) ;

                    if ( p.equals ( parent ) )
                    {
                        body += "<menu name=\"" + ( String ) arrayList.get ( arrayList2.size () - 2 ) + "\" id=\"1\">" ;
                    }
                    else
                    {
                        body += "<menu name=\"" + ( String ) arrayList.get ( arrayList2.size () - 2 ) + "\" id=\"1\"/>" ;
                    }
                }
            }

            if ( arrayList.size () > 0 && arrayList2.size () > 0 )
            {
                if ( flag3 )
                {
                    body += "<menu name=\"" + ( String ) arrayList.get ( arrayList2.size () - 1 ) + "\" id=\"1\"/></menu>" ;
                }
                else
                {
                    body += "<menu name=\"" + ( String ) arrayList.get ( arrayList2.size () - 1 ) + "\" id=\"1\"/>" ;
                }
            }

            System.out.println ( body ) ;
        }
        catch ( Exception e )
        {
            e.printStackTrace () ;
        }
        finally
        {
            try
            {
                if ( rset != null )
                    rset.close () ;
                if ( pstmt != null )
                    pstmt.close () ;
                if ( connection != null )
                    connection.close () ;
            }
            catch ( Exception ex )
            {
            }
        }
    }
}

And here is the output I am getting that is missing some of the end tags. And can't figure out how to handle this and have a valid file created with all the end tags.

<menu name="10" id="1">
  <menu name="60" id="1"/>
  <menu name="70" id="1">
    <menu name="100" id="1"/>
    <menu name="90" id="1">
      <menu name="110" id="1">
        <menu name="105" id="1"/>
        <menu name="106" id="1"/>
    <menu name="80" id="1"/>
</menu>
<menu name="200" id="1"/>
<menu name="300" id="1">
<menu name="301" id="1"/>
</menu>

Thanks