Piotr Woloszyn

Microsoft SQL Server does not allow to replace an existing XSD schema in a straight forward manner. There is a way of extending a schema by adding new schema component to an existing collection but it will not help if we need to replace a schema:

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

We cannot drop the schema collection and recreate it as it might be used by a table. The way forward would be to untype all XML columns using the collection, then drop and recreate the schema collection and as a last step type the XML columns to that new schema collection:

ALTER TABLE my_table ALTER COLUMN my_xml_column XML

DROP XML SCHEMA COLLECTION my_xml_collection

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

ALTER TABLE my_table ALTER COLUMN my_xml_column XML(my_xml_collection)

Popularity: 27% [?]

Share This