Wriju's BLOG

.NET, Cloud and everything

LINQ to XML : Join Xml Data

Let’s say I have created two Xml files using LINQ to XML from Northwind database. I have taken two tables Category and Products and tried to join between two different files.


 


Category XML


 


<?xml version=1.0 encoding=utf-8?>


<categories>


  <category id=1>


    <CategoryName>Beverages</CategoryName>


  </category>


  <category id=2>


    <CategoryName>Condiments</CategoryName>


  </category>


  <category id=3>


……


 


Products XML


 


<?xml version=1.0 encoding=utf-8?>


<products>


  <product ProductID=1 CategoryID=1>


    <ProductName>Chai</ProductName>


  </product>


  <product ProductID=2 CategoryID=1>


    <ProductName>Chang</ProductName>


  </product>


……


 


LINQ rocks here,


XElement prods =  XElement.Load(@”..\..\XmlData\Product.xml”);


XElement cats = XElement.Load(@”..\..\XmlData\Category.xml”);


 


var root =


    from p in prods.Descendants(“product”)


    join c in cats.Descendants(“category”)


    on


        (string)p.Attribute(“CategoryID”)


    equals


        (string)c.Attribute(“id”)


    select new


    {


        ProductId = (string)p.Attribute(“ProductID”),


        ProductName = (string)p.Element(“ProductName”),


        CategoryName = (string)c.Element(“CategoryName”)


    };


 


//Console.WriteLine(root.Count());


 


foreach (var k in root)


{


    Console.WriteLine(k);


}


 


Output will look like,


{ ProductId = 1, ProductName = Chai, CategoryName = Beverages }


{ ProductId = 2, ProductName = Chang, CategoryName = Beverages }


{ ProductId = 3, ProductName = Aniseed Syrup, CategoryName = Condiments }


 


If you want to create Xml file


 


var root = new XElement(“ProdList”,


    from p in prods.Descendants(“product”)


    join c in cats.Descendants(“category”)


    on


        (string)p.Attribute(“CategoryID”)


    equals


        (string)c.Attribute(“id”)


    select new XElement(“ProductCategory”,


new XAttribute(“ProductID”, (string)p.Attribute(“ProductID”)),


new XElement(“ProductName”, (string)p.Element(“ProductName”)),


new XElement(“CategoryName”, (string)c.Element(“CategoryName”))));


 


Console.WriteLine(root);


 


 


Output will look like,


<CategoryName>Produce</CategoryName>


</ProductCategory>


<ProductCategory ProductID=8>


  <ProductName>Northwoods Cranberry Sauce</ProductName>


  <CategoryName>Condiments</CategoryName>


</ProductCategory>


<ProductCategory ProductID=9>


  <ProductName>Mishi Kobe Niku</ProductName>


  <CategoryName>Meat/Poultry</CategoryName>


</ProductCategory>


…..


 


Namoskar!!!