Как стать автором
Обновить

Экспорт таблиц MS SQL Server 2000/2005/2008 в XML файл

Чулан

Здравствуйте, уважаемое хабрасообщество !


Поговорим о проблеме, ставшей заголовком этой темы.

Постановка:


Необходимость вывода таблицы на сервере в XML файл нужной кодировки для дальнейших нужд (анализ, включение XML в другие компоненты и приложения и т.п.). Будем использовать bat-сценарий.


Возникшие трудности


Отсутствие поддержки экспорта в различные кодировки средствами MS SQL.
SQL Server не сохраняет кодировку XML, если XML-данные постоянно хранятся в базе данных. Поэтому оригинальная кодировка полей XML недоступна при экспорте XML-данных. Для экспорта данных SQL Server использует кодировку UTF-16.
©
Простота и скорость использования для различных таблиц и баз.

Шаги реализации


Для начала, воспользуемся утилитой bcp, которая входит в комплект поставки MS SQL Server (Даже в Express версии). Подробрее.
Из ее возможностей нам понадобится только вывод результата запроса в файл.
Значения ключей на примере:
bcp "SELECT * FROM DB.SCHEMA.TABLE FOR XML AUTO, ROOT('ROOT')" queryout temp.xml -w -S %SERVERNAME% -U %DBUSER% -P %DBPASS%

Описание команды:

SELECT запрос для выборки всех данных из таблицы (указывается полное имя).
XML AUTO отвечает за преобразование результата в XML дерево.
ROOT назначает корневой элемент в этом дереве
queryout задает выходной файл
-w задает использование юникода для массового копирования
-S имя экземпляра сервера
-P пароль
-U пользователь

Этой командой мы получим XML файл БЕЗ заголовка в кодировке UTF-16.
Нужно присоединить заголовок и сделать xml нужной кодировки.
Создадим шаблон заголовка xml_header.xml с содержимым:
<?xml version="1.0" encoding="Windows-1251"?>

* This source code was highlighted with Source Code Highlighter.


Теперь достаточно будет выполнить команду
copy xml_header.xml + bcp_out.xml result.xml
и получить валидный XML документ.

Для преобразования кодировки же будем использовать iconv, любой реализации. Я выбрал самое компактное и портативное решение под Windows, написанное на Win32 API от Yukihiro Nakadaira.

Итак, файл сценария:

@echo off

if "%1" == "" (
rem Отсутстуют параметры
echo Use with: db_name db_table [out_file]
exit /b 1


if "%2" == "" (
echo Use with: db_name db_table [out_file]
exit /b 1


rem Читаем настройки из файла settings.txt, который должен располагаться в
rem том же каталоге, что и bat-файл. Если не удалось распарсить настройки -
rem выходим с ненулевым кодом возврата.
call :read_settings %~dp0settings.txt || exit /b 1

set DBNAME=%1
set DBTABLE=%2
set OUTFILE=%3

echo;
echo ====== ECHO SETTINGS FROM CONFIG ======
echo;
echo ServerName : %SERVERNAME%
echo Schema  : %SCHEMA%
echo Out codepage: %OUTCP%
echo User : %DBUSER%
echo Pass : ********
echo Iconv path : %ICONVPATH%
echo;
echo =======================================
echo;
echo ====== ECHO SETTINGS FROM CMD =========
echo;
echo DB Name = %1
echo DB Table = %2
echo Output file = %3
echo;
echo =======================================
echo;
echo ====== CALL TO BCP UTIL ===============
echo;
call :bcp_call
echo;
echo ====== CALL TO ICONV ==================
echo;
call :iconv_call
echo;
echo =======================================
echo;
echo See the log\log.txt for details
exit /b 0

rem
rem Функция для чтения настроек из файла.
rem Вход:
rem    %1      - Имя файла с настройками
:read_settings

set SETTINGSFILE=%1

rem Проверка существования файла
if not exist %SETTINGSFILE% (
  echo FAIL: No such file %SETTINGSFILE%
  exit /b 1
)

rem Обработка файла c настройками

for /f "eol=# delims== tokens=1,2" %%i in (%SETTINGSFILE%) do (
  set %%i=%%j
)

exit /b 0

rem
rem Функция для обращения к БД
:bcp_call

bcp "SELECT * FROM %DBNAME%.%SCHEMA%.%DBTABLE% FOR XML AUTO, ROOT('%DBTABLE%')" queryout temp.xml -w -r "" -S %SERVERNAME% -U %DBUSER% -P %DBPASS% > log\rawlog.txt

rem Кодируем лог в нормальную кодировку
%ICONVPATH% -f cp866 -t cp1251 log\rawlog.txt > log\log.txt
del log\rawlog.txt

copy lib\xml_header.xml + temp.xml temp2.xml > nul
del temp.xml

echo Finished.

exit /b 0

rem
rem Функция перекодировки
:iconv_call

rem Дефолтное значение выходной кодировки
if "%OUTCP%" == "" (
  set OUTCP=CP1251
)

rem Дефолтное значение выходного файла
if "%OUTFILE%" == "" (
  set OUTFILE=out\%DBTABLE%.xml
)

if not exist %ICONVPATH% (
  echo FAIL: Check Iconv path !
  exit /b /1
)

%ICONVPATH% -f UTF-16 -t %OUTCP% temp2.xml > %OUTFILE%
del temp2.xml

echo Finished.

exit /b 0


* This source code was highlighted with Source Code Highlighter.


Через параметры командной строки передаем: имя_базы имя_таблицы [выходной файл]
Остальную конфигурацию прописываем в settings.txt:

# Имя сервера
SERVERNAME=WIND\SQLEXPRESS
# Имя схемы
SCHEMA=dbo
# Имя выходной кодировки
OUTCP=CP1251

# Имя пользователя
DBUSER=dzhon
# Пароль пользователя
DBPASS=123

#Путь к iconv.exe
ICONVPATH=lib\win_iconv.exe


Для логики, в XML файле создано 3 функции.
Первая парсит конфигурацию, вторая вызывает bcp, третья — iconv для результирующего файла.

Заключение


Сразу замечу, что по-умолчанию используется CP1251 и лог работы bcp кодируется в нее же. Сделано это для удобства работы именно в Windows, а не каких-то иных религиозных предпочтений. Помню времена, когда Windows XP выдавала крокозябры при вызове простого route PRINT в описаниях интерфейсов… Поэтому я сам и предпочитаю всегда и везде UTF-8. Впрочем, скрипт достаточно гибок к замене кодировки выхода, правда придется задать в xml_header.xml другое значение.

Скачать архив с рабочим вариантом решения можно здесь (13 Кб).

Для SQL Server 2000 придется внести некоторые изменения, в связи с тем, что понятие схемы (в смысле контейнера объектов) было введено только с 2005-го.

Вы можете также посмотреть материал по написанию bat файлов, из которого была утащена функция парсинга конфигурации.

Спасибо за внимание, надеюсь, кому-то поможет.
Перенесу в тематический блог при наличии кармы, впрочем, решать вам.
Теги:
Хабы:
Всего голосов 6: ↑3 и ↓3 0
Просмотры 4.3K
Комментарии Комментарии 2