LINQ to XML : Join Xml Data

LINQ to XML : Join Xml Data

  • Comments 13

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

Leave a Comment
  • Please add 2 and 8 and type the answer here:
  • Post
  • Great postings

    just the right stuff

    keep it up

  • Great postings

    just the right stuff

    keep it up

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

  • How to remove the tag name from the output data?

  • @Scotty

    Could you please elaborate?

    - Wriju

  • 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

  • Sorry, something missing, the output data including table tag in html format.

  • @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

  • 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.

  • I am newbie for the C#, I try to test your LINQ to XML codes, everything is find, since I am using VWD, how can I display the result on the web browser using label text or text box, I tried it before, it only just show one line only!

    e.g.

    I use

    label1.text = k.ToString()

    instead of

    Console.WriteLine(k);

    so, how can I show a list result on webpage.

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

  • How to join 3 file xml? Help me!

  • DO you have sample file? May be then I can help you.

Page 1 of 1 (13 items)