Как экспортнуть XML в файл

Если вы не видите ссылки на оригинал, то этот материал позаимствован с блога https://blogs.msdn.com/alexejs/

А.Ш.

 

Предположим, на стороне SQL Server имеется некоторый XML, который мы хотим сохранить в виде файла. Неважно, откуда он взялся. Да хоть константа:

declare @x xml = '<root/>'

Принципиальными будут два условия: 1) процесс его получения достаточно объемный и превышает 8191 байт и 2) результат тоже здоровый и превышает 1052672 байт. Какие будут предложения?

Первый вариант достаточно очевиден. Пишется CLRная хр.пр., которая принимает входной параметр типа System.Data.SqlTypes.SqlXml, дергает единственный разумный метод этого класса SqlXml.CreateReader() и перекладывает получившийся XmlReader в XmlWriter или XmlDocument. Либо дергает единственное разумное свойство SqlXml.Value и переписывает строковое представление этого XML в файловый стрим. Тривиально. Поэтому наложим условие 3) CLR пользоваться нельзя.

Отдельные ушлые товарищи в SQL Server 2005 навострились использовать для этих целей обсолитную процедуру sp_makewebtask (https://msdn.microsoft.com/ru-ru/library/ms180099(SQL.90).aspx). Я думал, я один со времен 6.5 ее помню. Нет, смотрите, как народ на придумки горазд: https://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66647. Поэтому установим последнее условие 4) SQL Server 2008 и позже.

В этих условиях мое воображение ограничивается двумя утилитами командной строки – bcp и sqlcmd. Bcp (https://msdn.microsoft.com/en-us/library/ms162802.aspx) – это очень хорошая тула. Она умеет экспортить таблицы, вьюхи и результаты запросов в разные форматы, в том числе XML. Я помню, что во времена семерки параметр запрос в bcp имел ограничение по длине в 414 байт - https://support.microsoft.com/default.aspx/kb/279180. Потом это дело пофиксили и ограничение стало 1023 байта. В 2008-м благоразумно ничего не говорится, какой максимальной длины запрос можно bcpить. Однако я знаю, что ограничение на длину командной строки в ХР и выше составляет 8191 байт - https://support.microsoft.com/kb/830473, которые я оговорил в Условии 1. Скинуть здоровый запрос в файл и подсунуть этот файл в качестве параметра bcp по-прежнему нельзя. Дурдом. В качестве воркэраунда предлагается "Create a view that represents the query and use the view name instead of the query". А если в базе нет прав создавать вьюхи. Ну хорошо, вьюху можно в tempdb создать. Все равно не по-пацански. Отвергаем bcp из-за хронического нежелания воспринимать запрос из файла.

То ли дело sqlcmd (https://msdn.microsoft.com/en-us/library/ms162773.aspx). Эта тула заменила собой в 2005-м osql и isql. Тоже очень хорошая и интуитивно понятная. Я прямо так с ходу и написал:

sqlcmd -S (local) -d AdventureWorks -E -i c:\temp\Query.sql -o c:\Temp\Results.xml

Скрипт 1

где в Query.sql находится массивный (больше 8191 байта) SQLный скрипт, формирующий наш XML. Например, вот:

select * from Sales.SalesOrderDetail for xml auto

Скрипт 2

Ну неохота мне писать длинный SQLный скрипт. Представьте себе вместо этого запроса хранимую процедуру страниц на несколько кода. Представили? Тогда запускайте Скрипт 1. Смотрите, какая порнуха получилась в файле Results.xml.

 

рис.1

Бинарное представление результирующего XML в чистом виде. А нам бы не бинарное, нам бы текстовое. Вот здесь - https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23149084.html - народ основательно подошел к проблеме, аж на уровне TDS влезать собрался. У нас на это времени нет. Добавляем в Скрипте 2 директиву type:

select * from Sales.SalesOrderDetail for xml auto, type

Скрипт 3

 

Что она делает, можно прочитать в документации - https://msdn.microsoft.com/ru-ru/library/ms190025.aspx. "В SQL Server 2000 результат запроса FOR XML всегда возвращается непосредственно клиенту в текстовой форме. Начиная с версии SQL Server 2005, поддержка в SQL Server типа данных XML позволяет с помощью директивы TYPE запросить получение результата запроса FOR XML в виде типа данных xml. Это позволяет обрабатывать результат запроса FOR XML на сервере. Например, к нему можно применить инструкции на языке XQuery, присвоить его результат переменной типа xml..." и т.д. Прочитали? Выполняем строку Скрипт 1.

рис.2

Все бы хорошо, но обрезает, зараза. Обрезание происходит из-за того, что у sqlcmd ширина экрана ограничена по умолчанию 256 символами. Ширина экрана управляется параметром –y. Максимальное значение этого параметра 8000. Можно переписать Скрипт 1 как

sqlcmd -S (local) -d AdventureWorks -E -i c:\temp\Query.sql -o c:\Temp\Results.xml -y0

Скрипт 4

Нулевая ширина означает бесконечность. Бесконечность в данном случае означает 1052672 байта и ни битом больше. Если ваш XML укладывается в пределы 1 МБ, на этом можно остановиться. Если нет, извините, опять получится обрезание на полуслове.

 

рис.3

Читаем документацию (https://msdn.microsoft.com/ru-ru/library/ms162773.aspx) дальше. "Если для параметра «ширина_экрана» указано значение 0, происходит усечение размера выходных данных до 1 МБ. Чтобы предотвратить усечение данных, можно воспользоваться командой :XML ON. Эта команда описана далее в этом разделе". Супер. Ниже в командах контроля выполнения находим ":XML [ON | OFF] - Дополнительные сведения см. в подразделе «Формат выходных XML-данных» далее в этом разделе". И уже в самом конце появляется "Формат вывода XML. Выходные XML-данные, получаемые в результате выполнения предложения FOR XML, выводятся непрерывным потоком в неформатированном виде. Если ожидается вывод XML-данных, воспользуйтесь следующей командой: :XML ON". Я люблю нашу документацию за ее лаконичность. Зачем расписывать подробно с примерами, чтобы человек тратил свое ценное время на чтение. Пусть он лучше потратит его на развитие фантазии, додумывая, как же оно, черт возьми, должно выглядеть на самом деле, чтоб заработало, когда столкнется с этим на практике - https://social.msdn.microsoft.com/forums/en-US/sqlxml/thread/455d4b54-f322-49b7-b3de-b1108f8c7fe7/.

В действительности все обстоит в строгом соответствии с документацией. Как написано использовать :XML ON, так его и надо использовать. Перепишите Скрипт 3 так:

:xml on

select * from Sales.SalesOrderDetail for xml auto

Скрипт 5

Директиву type необходимо убрать. :XML ON работает с текстовым результатом запроса. Когда он вместо этого встречает XML, то пугается и выдает ошибку <?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?>.

рис.4

Запустите командную строку Скрипт 1:

рис.5

Получите нормальный неусеченный результат размером в 34 метра в файле Results.xml.

рис.6

Кстати, статусного вывода, что столько-то rows affected в данном случае нет, поэтому специально ставить set nocount on не надо.