LINQ to XML GroupBy

Question

I want to use LINQ to convert input XML to output XML by performing GROUPBY on "Summary" field and SUM up the Balance field.

Input XML:

<Root>
  <Account>
    <Summary>Checking</Summary>
    <Comprehensive>Interest Checking Account</Comprehensive>
    <Currency>Dollar</Currency>
    <Balance>10000000.000000</Balance>
  </Account>
  <Account>
    <Summary>Savings</Summary>
    <Comprehensive>Market Account</Comprehensive>
    <Currency>Dollar</Currency>
    <Balance>20000000.000000</Balance>
  </Account>
  <Account>
    <Summary>Checking</Summary>
    <Comprehensive>Interest Checking Account</Comprehensive>
    <Currency>Dollar</Currency>
    <Balance>50000000.000000</Balance>
  </Account>  
</Root>

Output XML:

<Root>
  <Account>
    <Summary>Checking</Summary>
    <Comprehensive>Interest Checking Account</Comprehensive>
    <Currency>Dollar</Currency>
    <Balance>60000000.000000</Balance>
  </Account>
  <Account>
    <Summary>Savings</Summary>
    <Comprehensive>Market Account</Comprehensive>
    <Currency>Dollar</Currency>
    <Balance>20000000.000000</Balance>
  </Account>
</Root>

I tried this but not able to get nodes/elements:

XElement groupData = new XElement("Root",
    chartData.Elements().GroupBy(x => x.Element("Summary").Value).
        Select(g => new XElement("Account", g.Key, g.Elements("Comprehensive"),
            g.Elements("Currency"),
            g.Sum(
                s =>
                (decimal)
                s.Element("Balance")))));

Any help would be appreciated. Thanks in advance.

Answer 1

In case you don't want intermediary objects you can use

    XDocument input = XDocument.Load("input.xml");
    XDocument output =
        new XDocument(
            new XElement(input.Root.Name,
                from account in input.Root.Elements("Account")
                group account by account.Element("Summary").Value into g
                select new XElement("Account",
                    g.ElementAt(0).Elements().Where(e => e.Name != "Balance"),
                    new XElement("Balance", g.Elements("Balance").Sum(b => (decimal)b)
                    ))));
    output.Save("output.xml");

or with method syntax you could use

    XDocument input = XDocument.Load(@"input.xml");
    XDocument output = new XDocument(
            new XElement(input.Root.Name,
                input.Root.Elements("Account")
                .GroupBy(a => a.Element("Summary").Value)
                .Select(g => new XElement("Account",
                    g.ElementAt(0).Elements().Where(e => e.Name != "Balance"),
                    new XElement("Balance", g.Elements("Balance").Sum(b => (decimal)b)
                    )))));
    output.Save("output.xml");

Answer 2

I would suggest projecting into normal objects, grouping them, then projecting back into XML:

var data = from acct in chartData.Elements()
           select new {
               Summary = (string)acct.Element("Summary"),
               Comprehensive = (string)acct.Element("Comprehensive"),
               Currency = (string)acct.Element("Currency"),
               Balance = (decimal)acct.Element("Balance"),
           };

var grouped = from acct in data
              group acct by acct.Summary into g
              select new {
                  Summary = g.Key,
                  Comprehensive = g.First().Comprehensive,
                  Currency = g.First().Comprehensive,
                  Balance = g.Sum(),
              };

var groupData = new XElement("Root",
     from g in grouped
     select new XElement("Account",
             new XElement("Summary", g.Summary),
             new XElement("Comprehensive", g.Comprehensive),
             new XElement("Currency", g.Currency),
             new XElement("Balance", g.Balance.ToString("0.000000"))
         )
     );