Revenant (3/3/2013)
First, IMO BoM is a wrong model for this one: in BoM you can have the same element on several different levels. For example, you may use the 8 #13 bolts to assemble an automatic transmission, then you use another 8 of the same bolts to mate the transmission to the engine, and finally use 10 bolts to attach the assembly to the frame of the car.This is not your case, I believe. Please correct me if I am wrong.
In theory, you're right about BoM using the same component at multiple levels within the same hierarchy being inaccurate to this case. At something in the range of 60,000 actual hierarchies to review, I'm not sure yet.
I would use CLR Integration and the power of XML LINQ to handle this, in a few lines of C# code. Not every SQLS installation allows that; if yours does, being in your shoes, I would go for it.
I can whip that code up for you. It might cost you a drink.
Unfortunately, my current location doesn't have SQL CLR activated, nor do I have the political coin to attempt to push it through for a non business critical item. I appreciate the offer though Revenant. If I ever get up in your area though, I'll still stand you that drink, just to pick your brain and hang with another SSC'er. 😀
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA