Best Practices for storing large amounts of XML type data in SQL Server

Question

Does anyone have any best practices they can share with regards to storing XML field type data in SQL Server 2008? We have lots of small XML structures, yet some larger (>50MB). We're finding that things get a little slow on DELETE. Any advice/war stories would be appreciated.

Answer 1

I see that most of the answers so far are for outside the database.

We have done this once, adding the file to the file system, and the name of the file in the a table in the database. The main problems with this was:

• the file system is not transactional, so it could get out of sync if something went wrong
• you had to take backup seperatly, and restore would by definition be out of sync

For all new projects we have stored files in varbinary(max) fields. This has worked well for us, also under loads of 10's of thousands of users.