Экспорт таблиц 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 файлов, из которого была утащена функция парсинга конфигурации.

    Спасибо за внимание, надеюсь, кому-то поможет.
    Перенесу в тематический блог при наличии кармы, впрочем, решать вам.

    Комментарии 2

      0
      скажите, а зачем это надо?
        0
        А кстати, есть способ экспортировать схему из БД MSSQL с командной строки? А то судя по документации положено полчаса мышкой тыкать.

      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

      Самое читаемое