sql to xml using linq - nested collections
- by nelsonwebs
I have a table of data that looks something like this.
name, hour, price1, price2, price3, price4, price5
fred, 3, 12.5, 13.5, 14, 15, 16
dave, 6, 8, 12, 18, 20.2, 25
fred, 6, 10, 11, 14, 15, 19.7
This table needs to be output to an xml file that looks like this.
<timeCost>
<person name="fred">
<time hour="5">
<cost price="12.5" />
<cost price="13.5" />
<cost price="14" />
<cost price="15" />
<cost price="16" />
</time>
<time hour="6">
<cost price="10" />
<cost price="11" />
<cost price="14" />
<cost price="15" />
<cost price="19.7" />
</time>
</person>
<person name="dave">
<time hour="6">
<cost price="8" />
<cost price="12" />
<cost price="18" />
<cost price="20.2" />
<cost price="25" />
</time>
</person>
</timeCost>
I have a linq query to get the data from SQL something like this.
// initialize data context
var people = from p in dc.people orderby p.name, p.hour select p;
However, I'm having trouble writing the xml out using linq (csharp). Specifically, the problem is I don't know how to deal with having multiple time nodes under each name node (nested loops/collections). How can this be done?
This is a sql 08 ent db if it matters to anyone.
