Thanks all for the feedback. Every environment is different so it's hard to anticipate what kind of loads such a process might need to handle. In my use it was for relatively small XML files used by a web service.

I think the main problem is that it uses several levels of recursion within the CTEs. I'll see what I can do to streamline the process. Any suggestions are welcome. I'm always open to improving the code.