Modifying SQL Server Schema Collection
- by Mevdiven
SQL Server XML Schema Collection is an interesting concept and I find it very useful when designing dynamic data content. However as I work my way through implementing Schema Collections, I find it very difficult to maintain them.
Schema Collection DDL allows only CREATE and ALTER/ADD nodes to existing schemes.
CREATE XML SCHEMA COLLECTION [ <relational_schema>. ]sql_identifier AS 'XSD Content'
ALTER XML SCHEMA COLLECTION [ <relational_schema>. ]sql_identifier ADD 'Schema Component'
When you want to remove any node from a schema you have to issue following DDL's.
If that schema collection assigned to a table column, you have to alter table to remove schema collection association from that column
Drop the schema collection object
Re-Create schema collection
Alter table column to re-associate schema collection to that column.
This is pain when it comes to 100+ of schemes in a collection. Also you have to re-create XML indexes all over again, if any.
Any solutions, suggestions, tricks to make this schema collection object editing process easier?
