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