Best way to represent n-level deep nested XMLnodes with same name?

Question

I'm writing an XML parser in C# .Net and for this particular chunk of XML I'm having a difficult time conceptualizing how I would parse and store it's relationship in a database. See sample xml below:

<Article name="x" date="y">
    <Words>
        <Category ID="1000" Name="Person">
            <Keyword ID="1124" Name="Adult">
                <Keyword ID="1125" Name="Female" />
            </Keyword>
        </Category>
        <Category ID="1000" Name="Person">
            <Keyword ID="1124" Name="Adult">
                  <Keyword ID="1126" Name="Male" />
            </Keyword>
        </Category>
    </Words>
</Article>

So you can see there are N Categories each containing N nested keyword nodes. What is the best way to represent this relational data?

I have a master XML doc of all the possible Keyword and Category combinations. This is my first thought but please correct me if there is a better way:

Table for all Keywords/Categories: ID, Name, ParentID
Table for Article: ID, Name, Date,
Go to bottom most keyword node in each category node and add to lookup table: ID, ArticleID, KeywordID

Then I could just do a join and recursively find all the parents of the bottom most keyword node.

You have xml, you have database table. What kind of representation you need? — Sergey Berezovskiy, Sep 16 '13 at 16:03
Actually, I guess my real question is how in C# would you get to the last descendant Keyword Node in each Category node? — jethomas, Sep 16 '13 at 16:54
You need to get `Male` and `Female` elements from this xml? Without getting `Adult` element? — Sergey Berezovskiy, Sep 16 '13 at 17:54
Correct. A way to only grab the bottom most node values.. ignoring the parent ones. — jethomas, Sep 17 '13 at 16:04

Sergey Berezovskiy · Accepted Answer · 2013-09-17T16:16:06.810

If you want to get only bottom most keywords, then you can simply check if keyword element contains any child nodes before selecting it:

var xdoc = XDocument.Load(path_to_xml);
var keywords = from k in xdoc.Descendants("Keyword")
               where !k.Elements().Any()
               select new
               {
                   ID = (int)k.Attribute("ID"),
                   Name = (string)k.Attribute("Name")
               };

Output:

{ ID = 1125, Name = Female }
{ ID = 1126, Name = Male }

UPDATE: XPath solution to get leaf Keyword elements

var keywords = xdoc.XPathSelectElements("//Keyword[not(*)]");

Best way to represent n-level deep nested XMLnodes with same name?

1 Answers1