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!!!