Advice on using (or not using) XML in a hierarchical object model

Question

I'm developing a hierarchical object model that is self-referencing as a 0/1 --> * relationship. An object without a parentID is a root element. The parentID is also the foreign key on the self-join. From my understanding, using the parentID as a foreign key will only point to a column where child elements may be found --> does this force an iteration through the entire data set for that column? Is this a scenario where a clustered index should be formed? ....would it be proper to use the XML data type to store all childrenIDs in a single field then load and reference that document for each object? It seems doing this would at least allow me to simplify my object persistence layer and give me more control over recording transactions.

Any advice?

Answer 1

I would strongly suggest against using XML to store the child IDs. It will cause countless headaches trying to maintain it down the road, not to mention trying to use it outside of your application (for example, from a reporting solution or for ETL).

Have you looked into the HIERARCHYID data type? It's in SQL 2008 and may be useful for you here. I don't know what kind of support the various programming languages/ODBC/OLE DB have for it, but you can convert it to a string with .ToString() and that can be manipulated pretty easily. It also then allows you to use the other methods of HIERARCHYID in T-SQL, like .GetAncestor(), etc.