代码之家 › 专栏 › 技术社区 › mevdiven

修改SQL Server架构集合

xml-schema-collection xsd database xml sql-server

mevdiven · 技术社区 · 16 年前

sqlserverxmlschema集合是一个有趣的概念,我发现它在设计动态数据内容时非常有用。然而,当我努力实现模式集合时,我发现维护它们非常困难。

架构集合DDL只允许创建和更改/向现有架构添加节点。

CREATE XML SCHEMA COLLECTION [ <relational_schema>. ]sql_identifier AS 'XSD Content'
ALTER XML SCHEMA COLLECTION [ <relational_schema>. ]sql_identifier ADD 'Schema Component'

当您想从模式中删除任何节点时,您必须发出以下DDL。

如果将该架构集合分配给表列,则必须更改表以从该列中删除架构集合关联
删除架构集合对象
重新创建架构集合
更改表列以将架构集合与该列重新关联。

当涉及到一个集合中的100+个方案时,这是一种痛苦。如果有的话,还必须重新创建XML索引。

1 回复 | 直到 16 年前

Laramie 16 年前

我同意David的观点,XML并不是我们被告知的万能药,但在某些情况下,它要么是不可避免的,要么是这项工作的最佳工具。然而,模式维护是痛苦的。我只有几件事要处理,但还是浪费了几个小时。

这个脚本可能会有帮助。它生成您需要的表拖放和添加。它需要mdified以包含udf或其他可能引用XML模式的对象。要生成addschema语句,我建议您使用mgtstudio中tasks菜单中的“generatescripts…”函数,并将它们保存到脚本的第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, @colName=COL, @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, @colName=COL, @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, @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 -1
END