I'm going to go ahead and mark this thread as resolved... I've got something that will fit my needs. Turns out I was over thinking and over engineering it once again. I discovered that I don't need to unpivot or union any of the data, I just needed to shred the XML back into a single row and then deal with it accordingly. It just took me being up half the night staring at it to figure this out. Once I unioned the data as I did above, I realized I was losing the context... and then I ended up with multiple rows in my result, which isn't the desired effect. Sigh.

So the solution ended up being: shred the XML into a single row, then join it to another CTE multiple times for the different segments. Part of what threw me was that the individual segment numbers (02-06) weren't in the same order I was expecting. The ordering is tracked in a different area, and the part of the system that creates these segments doesn't care what order they end up in, it just knows it needs to create the segments, and it's someone (or something) else's job to sort them into the right order. Yay.

So I'm marking this resolved. Sometimes all I need to do is just talk it out before the solution presents itself.

-tg