Post
Ask Question

Grouping Data in XML File using LINQ-XML

Suppose, I have below XML file:
  1. <?xml version="1.0" encoding="utf-8" ?>  
  2. <Products>  
  3.   <Product>  
  4.     <ID>1</ID>  
  5.     <Name>Milk</Name>  
  6.     <Category>Beverages</Category>  
  7.     <Price>20</Price>  
  8.     <Color>NA</Color>  
  9.   </Product>  
  10.   <Product>  
  11.     <ID>2</ID>  
  12.     <Name>Television</Name>  
  13.     <Category>Electronic</Category>  
  14.     <Price>2000</Price>  
  15.     <Color>Black</Color>  
  16.   </Product>  
  17.   <Product>  
  18.     <ID>3</ID>  
  19.     <Name>Inception</Name>  
  20.     <Category>Media</Category>  
  21.     <Price>200</Price>  
  22.     <Color>NA</Color>  
  23.   </Product>  
  24.   <Product>  
  25.     <ID>4</ID>  
  26.     <Name>Coke</Name>  
  27.     <Category>Beverages</Category>  
  28.     <Price>30</Price>  
  29.     <Color>NA</Color>  
  30.   </Product>  
  31.   <Product>  
  32.     <ID>5</ID>  
  33.     <Name>Samsung Note 3</Name>  
  34.     <Category>Electronic</Category>  
  35.     <Price>35000</Price>  
  36.     <Color>Blue</Color>  
  37.   </Product>  
  38.   <Product>  
  39.     <ID>6</ID>  
  40.     <Name>Dell Inspiron</Name>  
  41.     <Category>Electronic</Category>  
  42.     <Price>22000</Price>  
  43.     <Color>Red</Color>  
  44.   </Product>  
  45. </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:
  1. <?xml version="1.0" encoding="utf-8" standalone="yes"?>  
  2. <!--Products grouped by Category-->  
  3. <Products>  
  4.   <Beverages>  
  5.     <Product>  
  6.       <Name ID="1">Milk</Name>  
  7.       <Price>20</Price>  
  8.       <Color>NA</Color>  
  9.     </Product>  
  10.     <Product>  
  11.       <Name ID="4">Coke</Name>  
  12.       <Price>30</Price>  
  13.       <Color>NA</Color>  
  14.     </Product>  
  15.   </Beverages>  
  16.   <Electronic>  
  17.     <Product>  
  18.       <Name ID="2">Television</Name>  
  19.       <Price>2000</Price>  
  20.       <Color>Black</Color>  
  21.     </Product>  
  22.     <Product>  
  23.       <Name ID="5">Samsung Note 3</Name>  
  24.       <Price>35000</Price>  
  25.       <Color>Blue</Color>  
  26.     </Product>  
  27.     <Product>  
  28.       <Name ID="6">Dell Inspiron</Name>  
  29.       <Price>22000</Price>  
  30.       <Color>Red</Color>  
  31.     </Product>  
  32.   </Electronic>  
  33.   <Media>  
  34.     <Product>  
  35.       <Name ID="3">Inception</Name>  
  36.       <Price>200</Price>  
  37.       <Color>NA</Color>  
  38.     </Product>  
  39.   </Media>  
  40. </Products>  
And here is the LINQ-XML query to achieve this:
 
Query Syntax:
  1. var products = XDocument.Load("Products.xml");  
  2. var newProduct = new XDocument(new XDeclaration("1.0", "UTF-8", "yes"),  
  3.                                   new XComment("Products grouped by Category"),  
  4.                                   new XElement("Products",  
  5.                                   from p in products.Root.Descendants("Product")  
  6.                                   group p by p.Element("Category").Value into g  
  7.                                   select new XElement(g.Key,  
  8.                                   (from pro in g  
  9.                                    select new XElement(pro.Name,  
  10.                                           new XElement(pro.Element("Name").Name, pro.Element("Name").Value,  
  11.                                                new XAttribute(pro.Element("ID").Name, pro.Element("ID").Value)),  
  12.                                           new XElement(pro.Element("Price").Name, pro.Element("Price").Value),  
  13.                                           new XElement(pro.Element("Color").Name, pro.Element("Color").Value))  
  14.                                            ))));  
  15. newProduct.Save("ProductsByCategory.xml");  
Method Syntax: 
  1. var newProduct =  
  2.   new XDocument(new XDeclaration("1.0", "UTF-8", "yes"),  
  3.        new XComment("Products grouped by Category"),  
  4.            new XElement("Products",  
  5.             products.Root.Descendants("Product")  
  6.             .GroupBy(x => x.Element("Category").Value)  
  7.             .Select(x => new XElement(x.Key,  
  8.             x.Select(z => new XElement(z.Name,  
  9.              new XElement(z.Element("Name").Name, z.Element("Name").Value,  
  10.             new XAttribute(z.Element("ID").Name, z.Element("ID").Value)),  
  11.             new XElement(z.Element("Price").Name, z.Element("Price").Value),  
  12.             new XElement(z.Element("Color").Name, z.Element("Color").Value))  
  13.                                                    )))));  
  14. ewProduct.Save("ProductsByCategory.xml");  
Please let me know in case of any issues. Happy Coding :-)