sql Server
XML Schema Collection是一个有趣的概念,我发现在设计动态数据内容时非常有用.然而,当我通过实现模式集合工作,我觉得很难维护它们.
模式集合DDL仅允许CREATE和ALTER / ADD节点到现有方案.
CREATE XML SCHEMA COLLECTION [ <relational_schema>. ]sql_identifier AS 'XSD Content' ALTER XML SCHEMA COLLECTION [ <relational_schema>. ]sql_identifier ADD 'Schema Component'
当您要从模式中删除任何节点时,必须发出以下DDL.
>如果分配给表列的模式集合,则必须使用alter table从该列中删除模式集合关联
>删除模式集合对象
>重新创建模式集合
>更改表列以将模式集合重新关联到该列.
这是一个集合中的100个方案的痛苦.此外,您必须重新创建XML索引(如果有的话).
任何解决方案,建议,技巧,使此模式集合对象编辑过程更容易？
解决方法
我同意David的看法,XML不是我们被告知的灵丹妙药,但是有些情况是不可避免的,或者是工作的最佳工具.模式维护虽然很痛苦.我只有一对夫妇要处理,还会失去几个小时.
这个脚本可能有帮助.它会生成您需要的桌面下降和添加.它需要修改为包括可能引用XML模式的UDF或其他对象.要生成添加模式语句,我建议您在Mgt Studio的任务菜单中使用“生成脚本…”功能,并将其保存在脚本的步骤2中.
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_id
ORDER BY OBJECT_NAME(colm.object_id),colm.name
DECLARE @lastRow as int
DECLARE @currentRow as int
DECLARE @dbName as varchar(1024)
DECLARE @tableName as varchar(1024)
DECLARE @colName as varchar(1024)
DECLARE @schemaName as varchar(1024)
SET @lastRow = @@ROWCOUNT
SET @currentRow = @lastRow
SET @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 column
WHILE @currentRow <> 0
BEGIN
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 -1
END
print ''
print '--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
print '--!!!!! drop your xml schema(s) !!!!'
print '--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
print ''
SET @currentRow = @lastRow
WHILE @currentRow <> 0
BEGIN
SELECT @tableName=TBL,@schemaName=SCH from @xmlCols WHERE numID = @currentRow
print N'DROP XML SCHEMA COLLECTION [dbo].['+@schemaName+']'
set @currentRow = @currentRow -1
END
print ''
print '--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
print '--!!!!! CLean your Tables !!!!'
print '--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
print ''
--clean up the tables
SET @currentRow = @lastRow
WHILE @currentRow <> 0
BEGIN
SELECT @tableName=TBL,@schemaName=SCH from @xmlCols WHERE numID = @currentRow
print N'DBCC CleanTable (''' + @dbName + N''',''' + @tableName + N''',0)'
set @currentRow = @currentRow -1
END
print ''
print '--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
print '--!!!!! Run XML Schema Scripts !!!!'
print '--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
print ''
SET @currentRow = @lastRow
WHILE @currentRow <> 0
BEGIN
SELECT @tableName=TBL,@schemaName=SCH from @xmlCols WHERE numID = @currentRow
print N'ALTER TABLE [' + @tableName + N'] ALTER COLUMN ['+ @colName + N'] XML('+ @schemaName + N')'''
set @currentRow = @currentRow -1
END
希望它有帮助.
