Thanks for the article.

This will be applicable when there is a definite number of known levels like parents.Levels that i may have to traverse is not specific, also cross apply is an approach to operate on the exisitng data model where in we have all the parent and child in the same table and a column to differentiate between them.Rather i want to use the datatype hierarchyid as it has its own advantages as easier access to nodes with specific functions and mainly memory management.