Modifying SQL Server Schema Collection

I agree with David that XML is not the panacea we were told it would be, but there are some situations where it is either unavoidable or the best tool for the job. Schema maintenance is painful though. I only have a couple to deal with and still lose hours.

This script might help. It generates the table drops and adds you'll need. It would need to mdified to include UDFs or other objects that might reference the XML schema. To generate the Add schema statements, I suggest you use the "Generate Scripts..." function in the tasks menu in Mgt Studio and save them off for Step 2 of the script.

SET NOCOUNT ON/*     1) Save cols to table var*/DECLARE @xmlCols TABLE (numID INTEGER IDENTITY(1,1),TBL nvarchar(1024),COL nvarchar(1024),SCH nvarchar(1024));insert into @xmlCols (TBL,COL,SCH)SELECT DISTINCT OBJECT_NAME(colm.object_id) AS 'TABLE', colm.name AS 'COLUMN', coll.name AS 'Schema' FROM  sys.columns colm   inner JOIN     sys.xml_schema_collections coll        ON colm.xml_collection_id = coll.xml_collection_idORDER BY OBJECT_NAME(colm.object_id), colm.name   DECLARE @lastRow as intDECLARE @currentRow as intDECLARE @dbName as varchar(1024)DECLARE @tableName as varchar(1024)DECLARE @colName as varchar(1024)DECLARE @schemaName as varchar(1024)SET @lastRow = @@ROWCOUNTSET @currentRow = @lastRowSET @dbName = 'dbNAme'print ''print '--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'print '--!!!!! Scipt Schemas and Save in Mgt Studio !!!!'print '--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'print ''print ''print '--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'print '--!!!!! Omit Schemas from COls !!!!'print '--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'print ''--omit the Schema for each columnWHILE @currentRow <> 0BEGIN    SELECT @tableName=TBL, @colName=COL, @schemaName=SCH from @xmlCols WHERE numID = @currentRow    print N'ALTER TABLE [' + @tableName + N'] ALTER COLUMN ['+ @colName + N'] XML'    set @currentRow = @currentRow -1ENDprint ''print '--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'print '--!!!!! drop your xml schema(s)  !!!!'print '--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'print ''SET @currentRow = @lastRowWHILE @currentRow <> 0BEGIN    SELECT @tableName=TBL, @colName=COL, @schemaName=SCH from @xmlCols WHERE numID = @currentRow    print N'DROP XML SCHEMA COLLECTION [dbo].['+@schemaName+']'    set @currentRow = @currentRow -1ENDprint ''print '--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'print '--!!!!! CLean your Tables      !!!!'print '--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'print ''--clean up the tablesSET @currentRow = @lastRowWHILE @currentRow <> 0BEGIN    SELECT @tableName=TBL, @colName=COL, @schemaName=SCH from @xmlCols WHERE numID = @currentRow    print N'DBCC CleanTable (''' + @dbName + N''', ''' + @tableName + N''', 0)'    set @currentRow = @currentRow -1ENDprint ''print '--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'print '--!!!!! Run XML Schema Scripts !!!!'print '--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'print ''SET @currentRow = @lastRowWHILE @currentRow <> 0BEGIN    SELECT @tableName=TBL, @colName=COL, @schemaName=SCH from @xmlCols WHERE numID = @currentRow    print N'ALTER TABLE [' + @tableName + N'] ALTER COLUMN ['+ @colName + N'] XML('+ @schemaName + N')'''    set @currentRow = @currentRow -1END

Hope it helps.