Create XML using LINQ

Question

I have a table (datatable)which looks like this

 Hotelid    Room#   Description visitor Name    amount
    1          2    of             5    sam     10 
    1          2    of             5    sam      5
    1          2    of             5    sam     50
    1          2    of             8    james   50
    1          2    of             8    james   50
    1          2    of             6    justin  50
    2          3    sm             4    john     5
    2          4    al             3    jose     8
    3          5    ms             2    tim     10
    3          5    ms             7    tom     20

I want to create an XML out of it. I am using LINQ for this and I am totally confused and tired. I am not getting it

<Hotels>
    <Hotel id="1" room="2" description="of">
    <Room="2" descr="of" visitor="5" name="sam"/>
        <fine amount="10"/>
        <fine amount="5"/>
        <fine amount="50"/>
        <Room="2" descr="of" visitor="8" name="james"/>
         <fine amount="50"/>
        <fine amount="50"/>
        <Room="2" descr="of" visitor="6" name="justin"/>
        <fine amount="50"/>
        </hotel>
        <Hotel id="2" room="3" description="sm">
        <Room="3" descr="sm" visitor="4" name="john"/>
        <fine amount="5"/>
        </hotel>
        <Hotel id="2" room="4" description="al">
        <Room="4" descr="al" visitor="3" name="jose"/>
        <fine amount="8"/>
        </hotel>

        <Hotel id="3" room="5" description="ms">
        <Room="5" descr="ms" visitor="2" name="tim"/>
        <fine amount="10"/>
        <Room="5" descr="ms" visitor="7" name="tom"/>
        <fine amount="20"/>
        </hotel>
    </Hotels>

This is what my code looks like this

   var query =
    from row in Hotels.AsEnumerable()
    group row by new
    {
        Hotelid = row.Field<string>("Hotelid"),
        room = row.Field<string>("room"),
        descr = row.Field<string>("descr"),
    }
        into g
        select new XElement("Hotel",
                new XAttribute("Hotelid", g.Key.Hotelid),
                new XAttribute("room", g.Key.room),
                new XAttribute("desc", g.Key.desc),
                from row in g
                    select new XElement(
                    "Room",
                    new XAttribute("room", row.Field<string>("room#")),
                    new XAttribute("desc", row.Field<string>("desc")),
                    new XAttribute("visitor", row.Field<string>("visitor")),
                    new XAttribute("name", row.Field<string>("name")),
                from row in g 
                    select new XElement(
                    "fine",
                    new XAttribute("amount", row.Field<string>("amount"))));

                    var document = new XDocument(new XElement("Hotels", query));

But I am getting multiple nodes for "room" with the same value. Any help???? :(

Answer 1

hahaha, I think I've lost a little sanity coming up with this bad-boy. Let me know if it helps you.

void Main()
{
    var query =
    from row in Hotels.AsEnumerable()
    group row by new
    {
        row.Hotelid, row.Room, row.Description
    }
        into g
        select new XElement("Hotel",
                new XAttribute("Hotelid", g.Key.Hotelid),
                new XAttribute("room", g.Key.Room),
                new XAttribute("desc", g.Key.Description),
                from row in g
                    group row by new {row.Room, row.Description, row.Visitor, row.Name} into r
                    select new XElement(
                    "Room",
                    new XAttribute("room", r.Key.Room),
                    new XAttribute("desc", r.Key.Description),
                    new XAttribute("visitor", r.Key.Visitor),
                    new XAttribute("name", r.Key.Name),
                    new XAttribute("fineSum", r.Sum (x => x.Amount)),
                    from row in r                   
                    group row by new {row.Amount} into a
                    select new XElement("fine", a.Key.Amount )
              ));

    var document = new XDocument(new XElement("Hotels", query));                    

}

---

Sorry, forgot you're using a DataTable...

void Main()
{

    var query =
    from row in Hotels.AsEnumerable()
    group row by new
    {
        Hotelid = row.Field<string>("Hotelid"),
        room = row.Field<string>("room"),
        descr = row.Field<string>("Description")        
    }
        into g
        select new XElement("Hotel",
                new XAttribute("Hotelid", g.Key.Hotelid),
                new XAttribute("room", g.Key.room),
                new XAttribute("desc", g.Key.descr),
                from row in g
                    group row by new {
                        room = row.Field<string>("Room"),
                        descr = row.Field<string>("Description"), 
                        visitor = row.Field<string>("Visitor"), 
                        name = row.Field<string>("Name")
                    } into r
                    select new XElement(
                    "Room",
                    new XAttribute("room", r.Key.room),
                    new XAttribute("desc", r.Key.descr),
                    new XAttribute("visitor", r.Key.visitor),
                    new XAttribute("name", r.Key.name),
                    new XAttribute("fineSum", r.Sum (x => x.Field<int>("Amount"))),
                    from row in r                   
                    group row by new {fine = row.Field<int>("Amount")} into a
                    select new XElement("fine", a.Key.fine)
              ));

    var document = new XDocument(new XElement("Hotels", query));                    

}

// Define other methods and classes here