SQL Server first implemented XML Schema Collections in version 2005.
However, support for XML schema collections is not exactly developer-friendly: Once a schema collection has been created in a database, the ALTER XML SCHEMA COLLECTION command can only add to an existing schema, but nothing can be removed from it, or changed:
Use the ALTER XML SCHEMA COLLECTION to add new XML schemas whose namespaces are not already in the XML schema collection, or add new components to existing namespaces in the collection.
Change or removal of schema “components” (i.e. elements and attributes) is only possible by dropping and then re-creating the XML schema collection. This is not as straight-forward as it seems, and this lack of efficiency keeps developers from using XML-based data, as this Stack Overflow question shows.
To drop an XML schema collection, the following steps are necessary:
- If a table column references the schema collection (i.e. typed XML), it has to be converted to plain XML type
- If the table column has a default constraint, drop the default constraint
- If a procedure or function has typed XML parameters, the procedure or function has to be dropped
- If a function has typed XML parameters, the function has to be dropped
- If there is an XML index on a column referencing the schema collection, the index (primary and secondary indexes) has to be dropped
- If there are computed columns based on a typed XML column, the computed columns have to be dropped
- If there are indexes on these computed columns, the indexes have to be dropped
- If there are schema-bound views, functions, or procedures based on tables containing typed XML columns, these objects have to be dropped
Of course, all these DROP commands have to be executed in the correct order.
After creating the new XML schema collection, all the dropped objects can be re-created using information initially stored in the SQL Server catalog views.