Do also remember that your data tier is perhaps the hardest tier to scale out. By letting your SQL Server work with XML, you may take too much resources from it. That has somewhat changed with Distributed Partitioned Views in SQL Server 2000, but it's still hard to scale out since the management tools are really not there yet. To the clients, the table will look as one single table. To really benefit, you should add routing to your components before they hit the database servers.
Examples Let's take a look at two examples.
The examples will show how to send an XML document as a parameter to a stored procedure for further processing and the second example will return a rowset in the form of an XML document from another stored procedure. I therefore create an XML document with one person and a couple of status codes for that person. I then send the XML document as a parameter to a stored procedure for inserting the information to two different tables. I should not try to close if it wasn't opened. Otherwise I get an error.