Исправление XML схемы в MS SQL Server 2008/2008 R2

В SQL Server 2008 есть очень полезный тип XML. C помощью команды

CREATE XML SCHEMA COLLECTION [XML_схема] AS ...

можно создать xml схему, и использовать тип

XML(DOCUMENT [XML_схема])

для формирования «правильных» значений xml. Однако, xml схему уже нельзя изменить, если она используется; например, существует поле таблицы, параметр процедуры или функции созданного типа. В этом случае изменения можно внести двумя способами: прекратить использование схемы, внести изменения и восстановить параметры/типы полей; либо внести изменения в системные таблицы, в которых описана данная xml схема.

Явное исправление схемы
Первый вариант — заменить все использования типа XML(DOCUMENT [XML_схема]) на тип
VARCHAR(MAX) (для параметров процедур и функций подойдет также нетипизированный тип XML). Затем изменить схему с помощью команд

DROP XML SCHEMA COLLECTION и CREATE XML SCHEMA COLLECTION.

Для этого в SQL Management Studio есть удобный пункт меню DROP and CREATE To...

Вызов запроса для изменения xml схемы

Изменение xml схемы

В открытом окне запроса нужно указать полный текст схемы с внесенными изменениями.
Однако, этот способ подходит только в случае редкого использования данной xml схемы.

Исправление системных таблиц
После выполнения команды CREATE XML SCHEMA COLLECTION структура схемы хранится во множестве системных таблиц. Но в SQL Server 2008/2008 R2 запрещено править системные таблицы при обычном подключении. Для того, чтобы внести изменения, требуется сделать такие шаги.

1. Запустить данный экземпляр SQL server в single-user mode.

a. Добавить в разделе

HKLMSoftwareMicrosoftMicrosoft SQL ServerMSSQLxxx.yyyMSSQLServerParameters

строковый параметр SQLArgzzz типа REG_SZ со значением -m,
где xxx — номер версии (для SQL Server 2008: xxx = 10, а для SQL Server 2008 R2: xxx = 10_50),
yyy — имя экземпляра,
zzz — следующий порядковый номер параметра службы.

Ключи реестра для запуска службы в однопользовательском режиме

Ключи реестра

b. Убедиться, что служба SQL Server Browser запущена и стоит в автоматическом режиме.

c. Убедиться, что служба SQL Server Agent для этого экземпляра сервера отключена.

d. Перегрузить сервер, поскольку перезапуска служб не хватает.

2. Установить Dedicated Administrator Connection (DAC).

a. Запустить SQL Management Studio обычным способом. Использовать запуск от имени администратора не обязательно. Не подключаться к Database Engine.

b. Создать новый запрос. В диалоговом окне в качестве имени сервера указать ADMIN:Имя_экземпляра, база по умолчанию master. Вариант аутентификации пользователя может быть Windows/SQL, но учетная запись должна быть членом роли sysadmin.

Создание запроса с использованием административного подключения

Новый запрос

c. Появляется окно запроса. Можно выполнять запросы, модифицировать системные таблицы. Поскольку доступно подключение только одного пользователя, то нужно использовать только это окно запроса; просмотр дерева таблиц, процедур и других объектов в окне Object Explorer недоступен. Поэтому нужно сначала (либо в другом экземпляре SQL Server) просмотреть структуру таблиц, а затем с помощью запросов на T-SQL внести изменения.

3. Выполнить изменения таблиц.
Описание xml схем и их элементов находится в таблицах sys.xml_schema_***. Например,

select * from sys.xml_schema_collections

выводит список всех системных и пользовательских xml схем;

select * from sys.xml_schema_components where xml_collection_id = 65539

выводит элементы первой добавленной пользовательской xml схемы, и т.д.
В поисках необходимой таблицы очень помогает IntelliSense, встроенный в SQL Management Studio. Дальнейшие изменения выполняются с помощью DML команд. При этом следует внимательно следить за корректностью и целостностью изменяемых данных.

4. Восстановить прежние настройки сервера.
Требуется отключить окно запроса, закрыть студию, удалить параметр из реестра и перезапустить сервер. Должна восстановиться штатная работа SQL Server.
После этого просмотр xml схемы командой CREATE XML SCHEMA COLLECTION выведет схему с внесенными изменениями.


  1. Все используемые IP-адреса, имена серверов, компьютеров, доменов, являются фиктивными и используются исключительно в демонстрационных целях.
  2. Информация приводится «AS IS».
Pin It

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.