Alter Existing XML Collection

Alter Existing XML Collection in MS SQL Server

Trying to modify an existing XML Collection in MS SQL Server can be challenging unless you understand the work around. MS SQL Server does not allow replacing an existing XSD schema while it is being used by a table. There is a way of extending XML schemas by adding new schema components to existing collections but it will not help if we need to replace a schema:

ALTER XML SCHEMA COLLECTION [relational_schema.]sql_identifier ADD 'NWDS XML Schema'

We cannot drop schema collections and recreate them as they might be used by tables in your database. The easy way is to change the XML columns using the collection to nvarchar(MAX), then drop and recreate the schema collection. Finally,  change your XML columns back to using to that new schema collection by changing the column back to "xml" type and in the details section of the SQL Server Management Studio Design window, expand the "XML Type Specification" and assign the proper XML collection to your column.

ALTER TABLE my_table ALTER COLUMN nwds_xml_column XML

DROP XML SCHEMA COLLECTION nwds_xml_collection

CREATE XML SCHEMA COLLECTION my_xml_collection AS
'content of nwds XSD'

ALTER TABLE my_table ALTER COLUMN my_xml_column XML
(nwds_xml_collection)