Algorithm - create xml from structure without serialization

[Srcee]

I have two tables in my database - Category and Food. Food has idCategory. Category has id and idParentCategory.

From them, I need to create a string which will look like this (or xml):

<?xml version="1.0">
<tree id="0">
<item text="Category1">
<item text="Food1"/>
<item text="Food2"/>
</item>
<item text="Category2">
<item text="Category1">
<item text="Food3"/>
<item text="Food4"/>
</item>
<item text="Category3">
<item text="Food5"/>
<item text="Food6"/>
</item>
</item>
</tree>

How to create it? I tried finding the category which has id == 1, and then search all its children, but it's very complex. Is there an easy way?

One category can have many categories, but their children can have only food.

[Momerath]

Just do your select and order the results by parent category and category. Write a loop, break the results when one of them change.

For example, your SQL would be something like

SELECT a.Category as Parent, b.Category as Category, c.Food as Food
FROM Category a, Category b, Food c
WHERE a.id = b.idParentCategory AND b.id = c.idCategory
ORDER BY Parent, Category, Food

You'd then go through the results and 'break' on changes. Old COBOL style coding :)

Now you'll need to tweek that SQL statement as I used inner joins and you'll want outer joins as it appears that not every category has a parent (BTW, I don't like tables that link to themselves, you should have two tables, one for parent one for category, but that's me).

See what you can do with that and if you can't get it working right, post your code and we'll see what we can do.

[thines01]

Shouldn't the food items be another level deep (inside of a category)?

[Momerath]

Shouldn't the food items be another level deep (inside of a category)?

If he had indented them, the forum software removes leading spaces. Which is why code posted without code tags looks like junk :)

I don't like the use of 'item' to name every level, makes things confusing and will make the code hard to read (when you see it looking for elements of type 'item', which level is it looking at?)

[thines01]

If he had indented them, the forum software removes leading spaces.

Actually, I meant: Shouldn't it have another ID or shouldn't Category be its own element/node and the actual foods be an element underneath that?

Like this:

<?xml version="1.0">
<tree id="0">
<CATEGORY id="1" name="Vegetable">
	<food id="1" name="Lettuce"/>
	<food id="2" name="Cabbage"/>
	<food id="3" name="Okra"/>
</CATEGORY>
<CATEGORY id="2" name="Fruit">
	<food id="4" name="Tomato"/>
	<food id="5" name="Grape"/>
	<food id="6" name="Banana"/>
</CATEGORY>
<CATEGORY id="3" name="Meat">
	<food id="7" name="Chicken"/>
	<food id="8" name="Beef"/>
	<food id="9" name="Fish"/>
</CATEGORY>
</tree>

[Momerath]

Probably. But I believe his structure is like this:

<?xml version="1.0">
<tree id="0">
  <item text="Category1">
    <item text="Food1"/>
    <item text="Food2"/>
  </item>
  <item text="Category2">
    <item text="Category1">
      <item text="Food3"/>
      <item text="Food4"/>
    </item>
    <item text="Category3">
      <item text="Food5"/>
      <item text="Food6"/>
    </item>
  </item>
</tree>

Categories can have Categories (yuck).

[thines01]

Oh, I understand how it is. I WANT it to be different, so I'm planting seeds.

[thines01]

Please forgive this "overkill" example.
I have created a fun project for doing something like what you're looking for.
In all of this, I've probably overlooked a "SaveAsStructuredXml()" extension method somewhere, so I've done it manually.

I left in the Console.WriteLine() calls for cosmetic purposes only.

First: the result (exactly as I intended):

<?xml version="1.0" encoding="utf-8"?>
<tree id="0">
  <CATEGORY id="1" name="Vegetable">
    <food id="1" name="Lettuce" />
    <food id="2" name="Cabbage" />
    <food id="3" name="Okra" />
  </CATEGORY>
  <CATEGORY id="2" name="Fruit">
    <food id="4" name="Grapes" />
    <food id="5" name="Banana" />
    <food id="6" name="Tomato" />
  </CATEGORY>
  <CATEGORY id="3" name="Meat">
    <food id="7" name="Chicken" />
    <food id="8" name="Beef" />
    <food id="9" name="Fish" />
  </CATEGORY>
</tree>

Next: The Code:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Xml.Linq;

namespace DW_417709_CS_CON
{
   public class CCategory
   {
      public int intCategoryId { get; set; }
      public string strCategoryName { get; set; }
      
      public override string ToString()
      {
         return intCategoryId.ToString() + '-' + strCategoryName;
      }

      public override bool Equals(object obj)
      {
         return this.ToString().Equals(((CCategory)obj).ToString());
      }

      public override int GetHashCode()
      {
         return this.ToString().GetHashCode();
      }
   }

   public class CFood
   {
      public int intCategoryId { get; set; }
      public int intFoodId { get; set; }
      public string strFoodName { get; set; }

      public override string ToString()
      {
         return intFoodId.ToString()+'-'+ intCategoryId.ToString() + '-' + strFoodName;
      }

      public override bool Equals(object obj)
      {
         return this.ToString().Equals(((CFood)obj).ToString());
      }

      public override int GetHashCode()
      {
         return this.ToString().GetHashCode();
      }
   }

   class Program
   {
      static void Main(string[] args)
      {
         List<CCategory> lstCategories = new List<CCategory>()
         {
            new CCategory(){intCategoryId=1, strCategoryName="Vegetable"},
            new CCategory(){intCategoryId=2, strCategoryName="Fruit"},
            new CCategory(){intCategoryId=3, strCategoryName="Meat"}
         };

         List<CFood> lstFoods = new List<CFood>()
         {
            new CFood() {intFoodId=1, intCategoryId=1, strFoodName="Lettuce"},
            new CFood() {intFoodId=2, intCategoryId=1, strFoodName="Cabbage"},
            new CFood() {intFoodId=3, intCategoryId=1, strFoodName="Okra"},
            new CFood() {intFoodId=4, intCategoryId=2, strFoodName="Grapes"},
            new CFood() {intFoodId=5, intCategoryId=2, strFoodName="Banana"},
            new CFood() {intFoodId=6, intCategoryId=2, strFoodName="Tomato"},
            new CFood() {intFoodId=7, intCategoryId=3, strFoodName="Chicken"},
            new CFood() {intFoodId=8, intCategoryId=3, strFoodName="Beef"},
            new CFood() {intFoodId=9, intCategoryId=3, strFoodName="Fish"}
         };

         /*
          * **************************************************************
          * Everything up to this point is plumbing (mock construction).
          * **************************************************************
          */

         // Make it
         XDocument xd = new XDocument();
         xd.Add(new XElement("tree"));
         XElement xeTree = xd.Element("tree");
         xeTree.Add(new XAttribute("id", "0"));

         //ILookup<CCategory, CFood> lkupCategoryFoods = // MAGIC! MAGIC!! MAGIC!!!
         (
            from food in lstFoods
            join category in lstCategories on food.intCategoryId equals category.intCategoryId
            select new { category, food }
         ).ToLookup(k => k.category, v => v.food) // <<-- MAGIC! MAGIC!! MAGIC!!!
            .ToList().ForEach(grp =>
         {
            Console.WriteLine(grp.Key.intCategoryId.ToString() + ' ' + grp.Key.strCategoryName);
            xeTree.Add(new XElement("CATEGORY",
               new XAttribute[]{
                  new XAttribute("id", grp.Key.intCategoryId.ToString()),
                  new XAttribute("name", grp.Key.strCategoryName)}));

            XElement xeCurrentCategory = // MAGIC!
               xeTree.Elements().Where(xe => xe.Attribute("id").Value
                  .Equals(grp.Key.intCategoryId.ToString())).First();

            grp.ToList().ForEach(food =>{
               Console.WriteLine('\t' + food.intFoodId.ToString() + ' ' + food.strFoodName);
               xeCurrentCategory.Add(new XElement("food",
                  new XAttribute[]{
                     new XAttribute("id", food.intFoodId.ToString()),
                     new XAttribute("name", food.strFoodName)}));
            });
         });

         /////////////////////////////////////////////////////////
         // Save it in the temp directory
         // Saving it decorates the container with the XML header
         xd.Save(Path.Combine(Path.GetTempPath(), "testxml.xml"));
      }
   }
}

[thines01]

I know this is not exactly what was prescribed, but it can be modified (as it will be good practice).
I used Linq To XML for a portion of it because the elements have the same type only being separated by ID and name.
The real magic (and time-saver) comes in with adding an ARRAY of attributes to an element.

Please let me know if it is completely too complex. :)

[skatamatic]

I know this is not exactly what was prescribed, but it can be modified (as it will be good practice).
I used Linq To XML for a portion of it because the elements have the same type only being separated by ID and name.
The real magic (and time-saver) comes in with adding an ARRAY of attributes to an element.

Please let me know if it is completely too complex. :)

Haha if the OP doesn't understand how to write a simple XML file, what makes you think he will understand complex linq queries and lambda expressions like that?!

[thines01]

Haha if the OP doesn't understand how to write a simple XML file, what makes you think he will understand complex linq queries and lambda expressions like that?!

Yeah...
I could have just said: start with the XDocument and add attributes and elements as necessary.
...but I DID give a disclaimer. :) ;)