Altering XML Schema Collections

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: