Export MS SQL Server 2000/2005/2008 tables to an XML file

Hello, dear habrasoobschestvu!

Let's talk about the problem that has become the title of this topic.

Staging:

The need to output the table on the server to the XML file of the required encoding for further needs (analysis, inclusion of XML in other components and applications, etc.). We will use the bat script.

Difficulties encountered

No support for export to various encodings using MS SQL.

SQL Server does not preserve the XML encoding if the XML data is permanently stored in the database. Therefore, the original XML field encoding is not available when exporting XML data. SQL Server uses UTF-16 encoding to export data.

©
Simplicity and speed of use for various tables and bases.
')

Implementation steps

To begin with, we will use the bcp utility, which is included in the MS SQL Server distribution kit (Even in the Express version). More detail .
From its capabilities, we need only output the result of the query to the file.
Key values ​​by example:
bcp "SELECT * FROM DB.SCHEMA.TABLE FOR XML AUTO, ROOT('ROOT')" queryout temp.xml -w -S %SERVERNAME% -U %DBUSER% -P %DBPASS%

Command Description:

SELECT query to select all data from the table (the full name is indicated).
XML AUTO is responsible for converting the result to an XML tree.
ROOT assigns the root element in this tree.
queryout sets the output file
-w specifies unicode usage for bulk copying.
-S server instance name
-P password
-U user

This command will get the XML file WITHOUT the UTF-16 encoded header.
You need to attach a header and make the xml of the desired encoding.
Create a template xml_header.xml header with the contents:

<? xml version ="1.0" encoding ="Windows-1251" ? >

* This source code was highlighted with Source Code Highlighter .

Now it is enough to execute the command
copy xml_header.xml + bcp_out.xml result.xml
and get a valid XML document.

To convert the encoding, we will use iconv , of any implementation. I chose the most compact and portable solution for Windows, written on the Win32 API from Yukihiro Nakadaira .

So, the script file:

@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 .

Via the command line parameters we transfer: database_name table_name [output file]
We prescribe the rest of the configuration in settings.txt:

#
SERVERNAME=WIND\SQLEXPRESS
#
SCHEMA=dbo
#
OUTCP=CP1251

#
DBUSER=dzhon
#
DBPASS=123

# iconv.exe
ICONVPATH=lib\win_iconv.exe


For logic, 3 functions are created in the XML file.
The first parsit configuration, the second one calls bcp, the third one - iconv for the resulting file.

Conclusion

Immediately, I note that CP1251 is used by default and the bcp operation log is encoded into it. This is done for the convenience of work in Windows, and not some other religious preferences. I remember the times when Windows XP gave out crocodiles when calling simple route PRINT in interface descriptions ... Therefore, I myself prefer UTF-8 always and everywhere. However, the script is quite flexible to replace the output encoding, although you will have to set a different value in xml_header.xml .

Download the archive with the working version of the solution here (13 Kb) .

For SQL Server 2000, some changes will have to be made, due to the fact that the concept of a schema (in the sense of a container of objects) was introduced only from 2005.

You can also see the material on writing bat files, from which the configuration parsing function was dragged off.

Thank you for your attention, I hope someone will help.
Transfer to a thematic blog with the presence of karma, however, you decide.