QtSql write results to XML Hierarchy for List

[prophet0]

so my results for my SQL Query is

1000398|	Main
1000398|1100413|	   Network
1000398|1100414|	   Plans
1000398|1100415|	   Support
1000402|	Dine.To
1000403|	TourismTo
1100399|	Shopping
1100399|1100400|	   Store 1
1100399|1100401|	   Store 2
1100399|1100402|	   Store 3
1100399|1100403|	   Store 4
1100404|	News
1100404|1100405|	   Canada
1100404|1100406|	   Politics
1100404|1100407|	   Soccer
1100408|	Weather
1100409|	Hotels
1100409|1100410|	   Holiday Inn
1100409|1100411|	   Hilton

1000398|	Main
1000398|1100413|	   Network
1000398|1100414|	   Plans
1000398|1100415|	   Support
1000402|	Dine.To
1000403|	TourismTo
1100399|	Shopping
1100399|1100400|	   Store 1
1100399|1100401|	   Store 2
1100399|1100402|	   Store 3
1100399|1100403|	   Store 4
1100404|	News
1100404|1100405|	   Canada
1100404|1100406|	   Politics
1100404|1100407|	   Soccer
1100408|	Weather
1100409|	Hotels
1100409|1100410|	   Holiday Inn
1100409|1100411|	   Hilton

To copy to clipboard, switch view to plain text mode 

i am writing this data using QXmlStreamWriter

Like so

QXmlStreamWriter output(&file);
        output.setCodec("UTF-8");
        output.setAutoFormatting(true);
        output.writeStartDocument();
        output.writeStartElement("Menu");
 
        while (sqlSelFrmView.next())
        {
            output.writeStartElement("Model");
            output.writeTextElement("Name", sqlSelFrmView.value(1).toString());
            output.writeEndElement();
        }
 
        output.writeEndDocument();

QXmlStreamWriter output(&file);
        output.setCodec("UTF-8");
        output.setAutoFormatting(true);
        output.writeStartDocument();
        output.writeStartElement("Menu");

        while (sqlSelFrmView.next())
        {
            output.writeStartElement("Model");
            output.writeTextElement("Name", sqlSelFrmView.value(1).toString());
            output.writeEndElement();
        }

        output.writeEndDocument();

To copy to clipboard, switch view to plain text mode 

Sure this works but it write it in XML format as

<Menu> 
   <Model> 
      <Name>Main</Name>  
   </Model> 
   <Model> 
      <Name>	   Network</Name> 
   </Model>
</Menu>

<Menu> 
   <Model> 
      <Name>Main</Name>  
   </Model> 
   <Model> 
      <Name>	   Network</Name> 
   </Model>
</Menu>

To copy to clipboard, switch view to plain text mode 

now i need to do something a little different

based off of my sql root items are level = 1 and sub items are level = 2

how can i write the data as

<Menu> 
   <Model> 
      <Name>Main</Name>  // Level = 1
         <SubMenu>
           <Name>  	   Network</Name>   // Level = 2
         </SubMenu>
   </Model> 
</Menu>

<Menu> 
   <Model> 
      <Name>Main</Name>  // Level = 1
         <SubMenu>
           <Name>  	   Network</Name>   // Level = 2
         </SubMenu>
   </Model> 
</Menu>

To copy to clipboard, switch view to plain text mode 

[mentalmushroom]

this seems to be wrong: your level 1 is closed before you try to start level 2

[prophet0]

My query is as follows

if you have any way of improving it please feel free to direct me in the correct path

select concat(1000000 + a.id, '|') SORT, a.Name, a.Level
    from appmenu a
    where a.Level = 1
union all
    select concat(1000000 + a.ID, '|', 1000000 + IFNULL(b.ID,0), '|'),concat('   ', b.Name), b.Level
    from appmenu a
    inner join appmenu a1 on a1.ParentId = a.ID
    inner join appmenu b on b.ID = a1.ID
    where a.Level = 1
union all
    select concat(1000000 + a.ID, '|', 1000000 + IFNULL(b.ID,0), '|', 1000000 + IFNULL(c.ID,0), '|') ,concat('     ', c.Name), c.Level
    from appmenu a
    inner join appmenu a1 on a1.ParentId = a.ID
    inner join appmenu b on b.ID = a1.ID
    inner join appmenu b1 on b1.ParentId = b.ID
    inner join appmenu c on c.ID = b1.ID
    where a.Level = 1
union all
    select concat(1000000 + a.ID, '|', 1000000 + IFNULL(b.ID,0), '|', 1000000 + IFNULL(c.ID,0), '|', 1000000 + IFNULL(d.ID,0), '|'), concat('       ', d.Name),d.Level
    from appmenu a
    inner join appmenu a1 on a1.ParentId = a.ID
    inner join appmenu b on b.ID = a1.ID
    inner join appmenu b1 on b1.ParentId = b.ID
    inner join appmenu c on c.ID = b1.ID
    inner join appmenu c1 on c1.ParentId = c.ID
    inner join appmenu d on d.ID = c1.ID
    where a.Level = 1
union all
    select concat(1000000 + a.ID, '|', 1000000 + IFNULL(b.ID,0), '|', 1000000 + IFNULL(c.ID,0), '|', 1000000 + IFNULL(d.ID,0), '|', 1000000 + IFNULL(e.ID,0)) ,concat('         ', e.Name),e.Level
    from appmenu a
    inner join appmenu a1 on a1.ParentId = a.ID
    inner join appmenu b on b.ID = a1.ID
    inner join appmenu b1 on b1.ParentId = b.ID
    inner join appmenu c on c.ID = b1.ID
    inner join appmenu c1 on c1.ParentId = c.ID
    inner join appmenu d on d.ID = c1.ID
    inner join appmenu d1 on d1.ParentId = d.ID
    inner join appmenu e on e.ID = d1.ID
    order by SORT

select concat(1000000 + a.id, '|') SORT, a.Name, a.Level
    from appmenu a
    where a.Level = 1
union all
    select concat(1000000 + a.ID, '|', 1000000 + IFNULL(b.ID,0), '|'),concat('   ', b.Name), b.Level
    from appmenu a
    inner join appmenu a1 on a1.ParentId = a.ID
    inner join appmenu b on b.ID = a1.ID
    where a.Level = 1
union all
    select concat(1000000 + a.ID, '|', 1000000 + IFNULL(b.ID,0), '|', 1000000 + IFNULL(c.ID,0), '|') ,concat('     ', c.Name), c.Level
    from appmenu a
    inner join appmenu a1 on a1.ParentId = a.ID
    inner join appmenu b on b.ID = a1.ID
    inner join appmenu b1 on b1.ParentId = b.ID
    inner join appmenu c on c.ID = b1.ID
    where a.Level = 1
union all
    select concat(1000000 + a.ID, '|', 1000000 + IFNULL(b.ID,0), '|', 1000000 + IFNULL(c.ID,0), '|', 1000000 + IFNULL(d.ID,0), '|'), concat('       ', d.Name),d.Level
    from appmenu a
    inner join appmenu a1 on a1.ParentId = a.ID
    inner join appmenu b on b.ID = a1.ID
    inner join appmenu b1 on b1.ParentId = b.ID
    inner join appmenu c on c.ID = b1.ID
    inner join appmenu c1 on c1.ParentId = c.ID
    inner join appmenu d on d.ID = c1.ID
    where a.Level = 1
union all
    select concat(1000000 + a.ID, '|', 1000000 + IFNULL(b.ID,0), '|', 1000000 + IFNULL(c.ID,0), '|', 1000000 + IFNULL(d.ID,0), '|', 1000000 + IFNULL(e.ID,0)) ,concat('         ', e.Name),e.Level
    from appmenu a
    inner join appmenu a1 on a1.ParentId = a.ID
    inner join appmenu b on b.ID = a1.ID
    inner join appmenu b1 on b1.ParentId = b.ID
    inner join appmenu c on c.ID = b1.ID
    inner join appmenu c1 on c1.ParentId = c.ID
    inner join appmenu d on d.ID = c1.ID
    inner join appmenu d1 on d1.ParentId = d.ID
    inner join appmenu e on e.ID = d1.ID
    order by SORT

To copy to clipboard, switch view to plain text mode 

this gives the result of

ID# | Name | Level

1000398|	Telus		   1
1000398|1100413|	   Smartphones	  2
1000398|1100414|	   Rate Plans	  2
1000398|1100415|	   Coverage	  2
1000398|1100416|	   Support	          2
1000398|1100417|	   Promotions	  2
1000402|	Dine.To	   1
1000403|	TourismTo	   1
1100399|	Shopping	   1
1100399|1100400|	   Store 1	  	2
1100399|1100401|	   Store 2	  	2
1100399|1100402|	   Store 3	  	2
1100399|1100403|	   Store 4	  	2
1100404|	News	1
1100404|1100405|	   Canada	  	2
1100404|1100406|	   Politics	  	2
1100404|1100407|	   Soccer	  	2
1100408|	Weather	   1
1100409|	Hotels	   1
1100409|1100410|	   Holiday Inn	2
1100409|1100411|	   Hilton	  	2

1000398|	Telus		   1
1000398|1100413|	   Smartphones	  2
1000398|1100414|	   Rate Plans	  2
1000398|1100415|	   Coverage	  2
1000398|1100416|	   Support	          2
1000398|1100417|	   Promotions	  2
1000402|	Dine.To	   1
1000403|	TourismTo	   1
1100399|	Shopping	   1
1100399|1100400|	   Store 1	  	2
1100399|1100401|	   Store 2	  	2
1100399|1100402|	   Store 3	  	2
1100399|1100403|	   Store 4	  	2
1100404|	News	1
1100404|1100405|	   Canada	  	2
1100404|1100406|	   Politics	  	2
1100404|1100407|	   Soccer	  	2
1100408|	Weather	   1
1100409|	Hotels	   1
1100409|1100410|	   Holiday Inn	2
1100409|1100411|	   Hilton	  	2

To copy to clipboard, switch view to plain text mode 

[mentalmushroom]

MMh.. Maybe I incorrectly understood what you wanted to achieve, because your SubMenu node is shifted like a child of Name node.

QXmlStreamWriter output(&f);
 
    output.setCodec("UTF-8");
    output.setAutoFormatting(true);
    output.writeStartDocument();
    output.writeStartElement("Menu");
 
    output.writeStartElement("Model");
    output.writeTextElement("Name", "Main");
 
    output.writeStartElement("SubMenu");
    output.writeTextElement("Name", "Network");
    output.writeEndElement();
 
    output.writeEndElement();	//	model
 
    output.writeEndElement();	//	menu
 
    output.writeEndDocument();

QXmlStreamWriter output(&f);

    output.setCodec("UTF-8");
    output.setAutoFormatting(true);
    output.writeStartDocument();
    output.writeStartElement("Menu");

    output.writeStartElement("Model");
    output.writeTextElement("Name", "Main");

    output.writeStartElement("SubMenu");
    output.writeTextElement("Name", "Network");
    output.writeEndElement();

    output.writeEndElement();	//	model

    output.writeEndElement();	//	menu

    output.writeEndDocument();

To copy to clipboard, switch view to plain text mode 

[prophet0]

See im almost there but still wont work for me could anyone see my mistake

//XML stuff below
 
        QFile iconfile("/TouchTaxiMedia/iconList.xml");
        if(!iconfile.open(QIODevice::WriteOnly | QIODevice::Text))
        {
            qDebug() << "Failed to open file for write";
        }
 
        QXmlStreamWriter output2(&iconfile);
        output2.setCodec("UTF-8");
        output2.setAutoFormatting(true);
        output2.writeStartDocument();
        output2.writeStartElement("Menu");
 
        while (sqlSelFrmView.next())
        {
            if(sqlSelFrmView.value(2) == "1")
            {
                output2.writeStartElement("Model");
                output2.writeTextElement("Name", sqlSelFrmView.value(1).toString());
                if (!(sqlSelFrmView.value(3) == ""))
                {
                    output2.writeTextElement("Source", sqlSelFrmView.value(3).toString());
                }
                if (!(sqlSelFrmView.value(4) == ""))
                {
                    output2.writeTextElement("Picture", sqlSelFrmView.value(4).toString());
                }
                if(sqlSelFrmView.value(2) == "2")
                {
                    output2.writeStartElement("SubMenu");
                    output2.writeTextElement("Name", sqlSelFrmView.value(1).toString());
                    if (!(sqlSelFrmView.value(3) == ""))
                    {
                        output2.writeTextElement("Source", sqlSelFrmView.value(3).toString());
                    }
                    if (!(sqlSelFrmView.value(4) == ""))
                    {
                        output2.writeTextElement("Picture", sqlSelFrmView.value(4).toString());
                    }
                    output2.writeEndElement();
                }
                output2.writeEndElement();
 
            }
        }
 
        output2.writeEndDocument();
        //Done

//XML stuff below

        QFile iconfile("/TouchTaxiMedia/iconList.xml");
        if(!iconfile.open(QIODevice::WriteOnly | QIODevice::Text))
        {
            qDebug() << "Failed to open file for write";
        }

        QXmlStreamWriter output2(&iconfile);
        output2.setCodec("UTF-8");
        output2.setAutoFormatting(true);
        output2.writeStartDocument();
        output2.writeStartElement("Menu");

        while (sqlSelFrmView.next())
        {
            if(sqlSelFrmView.value(2) == "1")
            {
                output2.writeStartElement("Model");
                output2.writeTextElement("Name", sqlSelFrmView.value(1).toString());
                if (!(sqlSelFrmView.value(3) == ""))
                {
                    output2.writeTextElement("Source", sqlSelFrmView.value(3).toString());
                }
                if (!(sqlSelFrmView.value(4) == ""))
                {
                    output2.writeTextElement("Picture", sqlSelFrmView.value(4).toString());
                }
                if(sqlSelFrmView.value(2) == "2")
                {
                    output2.writeStartElement("SubMenu");
                    output2.writeTextElement("Name", sqlSelFrmView.value(1).toString());
                    if (!(sqlSelFrmView.value(3) == ""))
                    {
                        output2.writeTextElement("Source", sqlSelFrmView.value(3).toString());
                    }
                    if (!(sqlSelFrmView.value(4) == ""))
                    {
                        output2.writeTextElement("Picture", sqlSelFrmView.value(4).toString());
                    }
                    output2.writeEndElement();
                }
                output2.writeEndElement();

            }
        }

        output2.writeEndDocument();
        //Done

To copy to clipboard, switch view to plain text mode 

its not adding the submenu in there at all

[prophet0]

i put line 19 to line 14 and this fixes the submenu but now i cant model to close the secound time again cause its at line 14 if there a way i can get <model> to open imput data open <submenu> then input data then close submenu and close model then do it again untill end the code i gave above works only for the first record then it does not for anything else then closes to <menu>

[ChrisW67]

My query is as follows
...
if you have any way of improving it please feel free to direct me in the correct path

You may find this useful for storing a tree in an SQL database.