When you have to save hierarchical data inside a database you can traverse the tree with recursion and for each note call the database to save it. But that is not the most elegant solution; because your tree might be quite big and you can end up with lots of calls to the database. You may also need to wrap this whole thing inside a transaction but as we all know stretching a transaction between several calls is not the best approach.

What we actually want is to transfer the hierarchical tree inside a single stored procedure and process it there. But how can you pass a hierarchical data to a single stored procedure? Well the answer as often is XML. Let’s assume you have this hierarchical XML: