Thank you Eirikur. That was exactly what I looking for.

In terms of performance, Eirikur's solution performed better. It had less reads, most likely because it did not use a CTE....

Thanks Phil.

Was hoping to avoid using CTE as the actual xml contains ~100k requests with a couple more nodes that would require same CTE solution to pull data. Are there...