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

Comments

• Anonymous
  March 25, 2008
  Great postings just the right stuff keep it up

• Anonymous
  March 25, 2008
  Great postings just the right stuff keep it up

• Anonymous
  April 23, 2008
  Welcome to the forty-third issue of Community Convergence. The last few weeks have been consumed by the

• Anonymous
  July 04, 2008
  How to remove the tag name from the output data?

• Anonymous
  July 04, 2008
  @Scotty Could you please elaborate?

• Wriju

• Anonymous
  July 04, 2008
  As for your output data, how can I convert these data in html format as belows: e.g. CatagoryName   ProductID   ProductName ============   =========   =========== Condiments        1          Chai Condiments        2          Chang

• Anonymous
  July 04, 2008
  Sorry, something missing, the output data including table tag in html format.

• Anonymous
  July 04, 2008
  @Scotty, Do you want to read values from Database and then show them in HTML? Or do you want to read data from XML file and then show them in HTML? Both ways, you can bind an IList object to any DataGrid, or manually do a foreach and then build your own formatted HTML table.

• WRIJU

• Anonymous
  July 05, 2008
  Read data from XML file, the scenario same as your example, the output is formed to html style with table output. Have your above code can be applied on VWD2008, I want to use visual web developer 2008 express edition. Thanks.

• Anonymous
  July 26, 2008
  The comment has been removed

• Anonymous
  February 01, 2010
  Thank you for this post.  I was wondering, what is the best way of joining three or more xml files?

• Anonymous
  November 09, 2010
  How to join 3 file xml? Help me!

• Anonymous
  November 10, 2010
  DO you have sample file? May be then I can help you.

• Anonymous
  October 09, 2014
  Nice posting, Its really helped me. Thanks