PDA

View Full Version : QtSql write results to XML Hierarchy for List



prophet0
13th April 2012, 09:59
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


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();


Sure this works but it write it in XML format as




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


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>

mentalmushroom
13th April 2012, 12:07
this seems to be wrong: your level 1 is closed before you try to start level 2

prophet0
13th April 2012, 12:28
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


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

mentalmushroom
13th April 2012, 13:16
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();

prophet0
13th April 2012, 14:52
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


its not adding the submenu in there at all

prophet0
15th April 2012, 02:59
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
15th April 2012, 05:10
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 (http://www.sitepoint.com/hierarchical-data-database-2/) useful for storing a tree in an SQL database.