Изменение коллекции схем SQL Server

Question

Коллекция XML-схем SQL Server является интересной концепцией, и я считаю ее очень полезной при разработке динамического содержимого данных. Однако, работая над реализацией коллекций схем, мне очень трудно их поддерживать.

Коллекция схем 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.

1. Если эта коллекция схем назначена столбцу таблицы, необходимо изменить таблицу, чтобы удалить связь коллекции схем из этого столбца.
2. Удалить объект коллекции схемы
3. Пересоздать коллекцию схем
4. Измените столбец таблицы, чтобы повторно связать коллекцию схем с этим столбцом.

Это боль, когда речь идет о 100+ схемах в коллекции. Также вам придется заново создавать XML-индексы, если таковые имеются.

Какие-либо решения, предложения, приемы, облегчающие процесс редактирования объекта коллекции схем?

Answer 1

Я согласен с Дэвидом, что XML не является панацеей, о которой нам говорили, что это будет, но в некоторых ситуациях он неизбежен или является лучшим инструментом для работы. Поддержание схемы болезненно, хотя. Я имею дело только с парой и все еще теряю часы.

Этот скрипт может помочь. Он генерирует таблицы и добавляет, что вам нужно. Это должно быть модифицировано, чтобы включить UDF или другие объекты, которые могут ссылаться на XML-схему. Чтобы сгенерировать операторы схемы Add, я предлагаю вам использовать функцию "Generate Scripts..." в меню задач в 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, @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

Надеюсь, поможет.