Converting nested collection into flat Data Table

Question

I have a nested collection of values (ID and Name) that are stored as XML. I would like to convert this XML into a single Data Table while maintaining the parent child relationship.

<Apartment id="1A" name="Apartment 1A">
    <ApartmentComponent id="300" name="Living Room" />
    <ApartmentComponent id="301" name="Bathroom">
        <ApartmentComponent id="2698" name="Tub" />
        <ApartmentComponent id="8204" name="Sink" />
    </ApartmentComponent>
</Apartment>
<Apartment id="2A" name="Apartment 2A">
    <ApartmentComponent id="302" name="Dining Room">
        <ApartmentComponent id="2635" name="Table" />
        <ApartmentComponent id="2746" name="Cabinet" />
    </ApartmentComponent>
    <ApartmentComponent id="301" name="Bathroom">
        <ApartmentComponent id="8204" name="Sink">
            <ApartmentComponent id="56352" name="Drain Plug" />
        </ApartmentComponent>
    </ApartmentComponent>
</Apartment>

The resulting table would look like this ...

+-------+--------------+-----------+--------------+
|   ID  |     Value    | Parent ID | Parent Value |
+-------+--------------+-----------+--------------+
|   1A  | Apartment 1A |           |              |
+-------+--------------+-----------+--------------+
|  300  |  Living Room |     1A    | Apartment 1A |
+-------+--------------+-----------+--------------+
|  301  |   Bathroom   |     1A    | Apartment 1A |
+-------+--------------+-----------+--------------+
|  2698 |      Tub     |    301    |   Bathroom   |
+-------+--------------+-----------+--------------+
|  8204 |     Sink     |    301    |   Bathroom   |
+-------+--------------+-----------+--------------+
|   2A  | Apartment 2A |           |              |
+-------+--------------+-----------+--------------+
|  302  |  Dining Room |     2A    | Apartment 2A |
+-------+--------------+-----------+--------------+
|  2635 |     Table    |    302    |  Dining Room |
+-------+--------------+-----------+--------------+
|  2746 |    Cabinet   |    302    |  Dining Room |
+-------+--------------+-----------+--------------+
|  301  |   Bathroom   |     2A    | Apartment 2A |
+-------+--------------+-----------+--------------+
|  8204 |     Sink     |    301    |   Bathroom   |
+-------+--------------+-----------+--------------+
| 56352 |  Drain Plug  |    8204   |     Sink     |
+-------+--------------+-----------+--------------+

I would typically start by parsing the XML using XDocument. This would give me a collection that I could query using LINQ. However I am not sure how to parse the resultant collection into a flat table structure. Will I need to make use of a recursive function?

Answer 1

This approach can also be your option to get the solution you need.

PHASE 1: Deserialize

Class Objects

Note: To also understand ChildItemCollection class this was a good article for serialization of XML with parent child relationship intact. You can check it here C# Parent/child relationship and XML serialization

[XmlRoot("root")]
public class Apartment
{
    public Apartment()
    {
        this.Children = new ChildItemCollection<Apartment, ApartmentComponent>(this);
    }

    [XmlAttribute("id")]
    public string Id { get; set; }

    [XmlAttribute("name")]
    public string Name { get; set; }

    [XmlElement("ApartmentComponent")]
    public ChildItemCollection<Apartment, ApartmentComponent> Children { get; private set; }
}

public class ApartmentComponent : IChildItem<Apartment>, IChildItem<ApartmentComponent>
{
    public ApartmentComponent()
    {
        this.Children = new ChildItemCollection<ApartmentComponent, ApartmentComponent>(this);
    }

    [XmlAttribute("id")]
    public string Id { get; set; }

    [XmlAttribute("name")]
    public string Name { get; set; }

    [XmlElement("ApartmentComponent")]
    public ChildItemCollection<ApartmentComponent, ApartmentComponent> Children { get; private set; }

    [XmlIgnore]
    public Apartment ParentApartment { get; internal set; }

    Apartment IChildItem<Apartment>.Parent
    {
        get
        {
            return this.ParentApartment;
        }
        set
        {
            this.ParentApartment = value;
        }
    }

    [XmlIgnore]
    public ApartmentComponent ParentComponent { get; internal set; }
    ApartmentComponent IChildItem<ApartmentComponent>.Parent
    {
        get
        {
            return this.ParentComponent;
        }
        set
        {
            this.ParentComponent = value;
        }
    }
}

Implementation

XmlSerializer serializer = new XmlSerializer(typeof(List<Apartment>), new XmlRootAttribute("root"));
        using (FileStream fileStream = new FileStream("XMLFile1.xml", FileMode.Open))
        {
            var result = (List<Apartment>)serializer.Deserialize(fileStream);
        }

PHASE 2: Flattening into flat data.
It's quite complex and this the approach I can give into. But hopefully there are other alternatives that much better.

Class Objects

public class ResultItem
{
    public string Id { get; set; }
    public string Value { get; set; }
    public string ParentId { get; set; }
    public string ParentValue { get; set; }
}

Implementation

 XmlSerializer serializer = new XmlSerializer(typeof(List<Apartment>), new XmlRootAttribute("root"));
        using (FileStream fileStream = new FileStream("XMLFile1.xml", FileMode.Open))
        {
            var result = (List<Apartment>)serializer.Deserialize(fileStream);
            var items = result.Select(item => new ResultItem {
                Id = item.Id,
                Value = item.Name
            }).ToList();

            result.ForEach(apartment =>
            {
                apartment.Children.ToList().ForEach(component => {
                    setItemResult(component, items);
                });
            });

            dataGridView1.DataSource = items;
        }

private void setItemResult(ApartmentComponent apartmentComponent, List<ResultItem> items)
    {
        if (apartmentComponent.Children.Count > 0)
        {
            apartmentComponent.Children.ToList().ForEach(component => {
                setItemResult(component, items);
            });
        }

        var item = new ResultItem
        {
            Id = apartmentComponent.Id,
            Value = apartmentComponent.Name
        };

        var parentApartment = apartmentComponent.ParentApartment;
        var parentComponent = apartmentComponent.ParentComponent;

        if (parentApartment != null)
        {
            item.ParentId = parentApartment.Id;
            item.ParentValue = parentApartment.Name;
        }

        if (parentComponent != null)
        {
            item.ParentId = parentComponent.Id;
            item.ParentValue = parentComponent.Name;
        }

        items.Add(item);
    }

OUTPUT:

Happy coding, cheers!

Answer 2

Like madreflection commented above, XContainer.Descendants() will do this without the need for recursion - or rather it will handle the recursion for you, going all the way to the drain plug and beyond.

Assuming there is a <root> element surrounding that snippet so that XDocument will parse it normally,

foreach (var element in xdoc.Root.Descendants())
{
    (string, string, string, string) values = (
        element.Attribute("id").Value,
        element.Attribute("name").Value,
        element.Parent?.Attribute("id")?.Value,
        element.Parent?.Attribute("name")?.Value
    );
    Console.WriteLine(values);
}