Grouping Data in XML File using LINQ-XML

In this blog, I will try to explain how to transform a XML file to another XML by grouping the main XML file.

Suppose, I have below XML file:

<?xml version="1.0" encoding="utf-8" ?>
<Products>
  <Product>
    <ID>1</ID>
    <Name>Milk</Name>
    <Category>Beverages</Category>
    <Price>20</Price>
    <Color>NA</Color>
  </Product>
  <Product>
    <ID>2</ID>
    <Name>Television</Name>
    <Category>Electronic</Category>
    <Price>2000</Price>
    <Color>Black</Color>
  </Product>
  <Product>
    <ID>3</ID>
    <Name>Inception</Name>
    <Category>Media</Category>
    <Price>200</Price>
    <Color>NA</Color>
  </Product>
  <Product>
    <ID>4</ID>
    <Name>Coke</Name>
    <Category>Beverages</Category>
    <Price>30</Price>
    <Color>NA</Color>
  </Product>
  <Product>
    <ID>5</ID>
    <Name>Samsung Note 3</Name>
    <Category>Electronic</Category>
    <Price>35000</Price>
    <Color>Blue</Color>
  </Product>
  <Product>
    <ID>6</ID>
    <Name>Dell Inspiron</Name>
    <Category>Electronic</Category>
    <Price>22000</Price>
    <Color>Red</Color>
  </Product>
</Products>

Now, I want to transform this XML file to another XML file but the products should be grouped based on category, also I want few formatting like ID of the product should appear as attribute of product name, so my transformed XML should look like this:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<!--Products grouped by Category-->
<Products>
  <Beverages>
    <Product>
      <Name ID="1">Milk</Name>
      <Price>20</Price>
      <Color>NA</Color>
    </Product>
    <Product>
      <Name ID="4">Coke</Name>
      <Price>30</Price>
      <Color>NA</Color>
    </Product>
  </Beverages>
  <Electronic>
    <Product>
      <Name ID="2">Television</Name>
      <Price>2000</Price>
      <Color>Black</Color>
    </Product>
    <Product>
      <Name ID="5">Samsung Note 3</Name>
      <Price>35000</Price>
      <Color>Blue</Color>
    </Product>
    <Product>
      <Name ID="6">Dell Inspiron</Name>
      <Price>22000</Price>
      <Color>Red</Color>
    </Product>
  </Electronic>
  <Media>
    <Product>
      <Name ID="3">Inception</Name>
      <Price>200</Price>
      <Color>NA</Color>
    </Product>
  </Media>
</Products>

And here is the LINQ-XML query to achieve this:

 

Query Syntax:

var products = XDocument.Load("Products.xml");
var newProduct = new XDocument(new XDeclaration("1.0", "UTF-8", "yes"),
                                  new XComment("Products grouped by Category"),
                                  new XElement("Products",
                                  from p in products.Root.Descendants("Product")
                                  group p by p.Element("Category").Value into g
                                  select new XElement(g.Key,
                                  (from pro in g
                                   select new XElement(pro.Name,
                                          new XElement(pro.Element("Name").Name, pro.Element("Name").Value,
                                               new XAttribute(pro.Element("ID").Name, pro.Element("ID").Value)),
                                          new XElement(pro.Element("Price").Name, pro.Element("Price").Value),
                                          new XElement(pro.Element("Color").Name, pro.Element("Color").Value))
                                           ))));
newProduct.Save("ProductsByCategory.xml");

Method Syntax: 

var newProduct =
  new XDocument(new XDeclaration("1.0", "UTF-8", "yes"),
       new XComment("Products grouped by Category"),
           new XElement("Products",
            products.Root.Descendants("Product")
            .GroupBy(x => x.Element("Category").Value)
            .Select(x => new XElement(x.Key,
            x.Select(z => new XElement(z.Name,
             new XElement(z.Element("Name").Name, z.Element("Name").Value,
            new XAttribute(z.Element("ID").Name, z.Element("ID").Value)),
            new XElement(z.Element("Price").Name, z.Element("Price").Value),
            new XElement(z.Element("Color").Name, z.Element("Color").Value))
                                                   )))));
ewProduct.Save("ProductsByCategory.xml");

Please let me know in case of any issues. Happy Coding :-)