Exporting MS SQL Server 2000/2005/2008 tables to XML
Let’s talk about the problem, which became the title of the topic.
Production:
The need for the withdrawal of the table on the server in the XML file encoding you want for future needs (analysis, integration of XML into other components and applications, etc.). We use the bat-script.
Difficulties
Lack of support for exports to various encoding tools MS SQL.
SQL Server does not store the encoding XML, where XML-data permanently stored in the database. Therefore, the original XML encoding of fields available for export XML-data. To export a SQL Server uses the UTF-16.
©
The simplicity and speed of use for the various tables and databases.
Steps of implementation
First, we use the utility bcp, which included MS SQL Server (Even in the Express version). Podrobree.
Because of its capabilities, we need only the result output request to a file.
The values of keys on the example:
bcp “SELECT * FROM DB.SCHEMA.TABLE FOR XML AUTO, ROOT ( ‘ROOT’)” queryout temp.xml-w-S% SERVERNAME%-U% DBUSER%-P% DBPASS%
Description of team:
SELECT query to retrieve all data from the table (the full name).
XML AUTO is responsible for converting the result to an XML tree.
ROOT appoints the root element in this tree
queryout specifies the output file
-w specifies the use of Unicode for mass copying
-S name of the server instance
-P password
-U user
Using this command we get the XML file without a header in the UTF-16.
It is necessary to add a title and make the correct xml encoding.
Create a template header xml_header.xml with the contents:
* This source code was highlighted with Source Code Highlighter.
Now it will be sufficient to execute the command
copy xml_header.xml + bcp_out.xml result.xml
and get a valid XML document.
To convert the encoding is going to use iconv, any implementation. I chose the most compact and portable solution for Windows, written in Win32 API from Yukihiro Nakadaira.
So, the script file:
@ echo off
if "% 1" == "" (
The absence rem options
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 We read the settings from the file settings.txt, which must be located in
rem the same directory as the bat-file. If you can not parse configuration --
rem exit with a nonzero return code.
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 Function to read the settings from a file.
rem Login:
rem% 1 - file name settings
: read_settings
set SETTINGSFILE =% 1
rem Check file exists
if not exist% SETTINGSFILE% (
echo FAIL: No such file% SETTINGSFILE%
exit / b 1
)
rem Processing file c settings
for / f "eol = # delims == tokens = 1,2"%% i in (% SETTINGSFILE%) do (
set%% i =%% j
)
exit / b 0
rem
rem function to access the database
: 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 encoding the log in normal encoding
% 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 Function conversion
: iconv_call
rem a default value of the output encoding
if “% OUTCP%” == “” (
set OUTCP = CP1251
)
rem a default value of the output file
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.
A command-line pass: DatabaseName tbl_name [output file]
The rest of the configuration prescribes in settings.txt:
# Server Name
SERVERNAME = WIND \ SQLEXPRESS
# Name of scheme
SCHEMA = dbo
# Name the output encoding
OUTCP = CP1251
# User
DBUSER = dzhon
# User password
DBPASS = 123
# Path to iconv.exe
ICONVPATH = lib \ win_iconv.exe
For the logic in the XML file created 3 functions.
First parses the configuration, the second is bcp, the third - iconv for the resulting file.
Conclusion
Immediately, I note that by default uses CP1251, and the log of the bcp is encoded in it the same. This is done for the convenience of it is in Windows, and not some other religious preference. I remember the days when Windows XP betrayed krokozyabry when calling a simple route PRINT in the descriptions of the interfaces … So I myself prefer to always and everywhere UTF-8. However, the script is flexible enough to change the encoding output, though will have to ask in xml_header.xml another value.
Download the working version of the solution here (13 Kb).
For SQL Server 2000 will have to make some changes due to the fact that the concept of the scheme (in terms of container facilities) has been introduced only in 2005.
You can also see the material on writing bat files, which had been dragged from parsing configuration.
Thank you for your attention, I hope someone can help.
Moved to a thematic blog in the presence of karma, however, you decide.
—
Check out the analysis of pantswalker.net, 3t.cn, interpark.com, bollyfm.net, land.to, animefreak.tv, asiafriendfinder.com, vuku.ru, 91.cn, bloggang.com - and much more
