Introduction

Microsoft Excel can open XML files or use it as a refreshable data source.

So we can use SQL Server data export to XML to deliver the data to Excel users.

This article contains a solution for SQL Server data export to XML using bcp/sqlcmd utilities.

Note:

  • sqlcmd can be used to export data using the only input SQL query file.
  • bcp can be used to export data using the only inline SQL query.

Bonus

I am also an author of the gsqlcmd command line utility.
It allows exporting data to plain text, CSV, and HTML much easily.
You may download and use it for free.

Table of Contents

SQL Server Data Export to XML using SQLCMD

sqlcmd is an SQL Server command line utility.

sqlcmd can be downloaded separately with the SQL Server Feature Pack. See links below.

The basis export command for sqlcmd:

sqlcmd -S . -d AzureDemo50 -E -i ExcelTest.sql > ExcelTest.xml

where

-S .
Defines the localhost server. For a named instance you can use a parameter like .\SQLEXPRESS.
-d AzureDemo50
Defines the database AzureDemo50.
-E
Defines the trusted connection. Instead, you can use user credentials: -U Username -P Password
-i ExcelTest.sql
Defines an input SQL query file.
> ExcelTest.xml
Outputs the result to file ExcelTest.xml.

The magic is in the SQL query of the ExcelTest.sql:

:XML ON

SELECT
    *
FROM
    dbo04.ExcelTest
FOR XML AUTO, ELEMENTS, ROOT('doc')

Important! The sqlcmd utility requires :XML ON sqlcmd command on the single line!

The result of the SQL query above:

<doc>
<dbo04.ExcelTest>
    <ID>1</ID>
    <Float>123.4567</Float>
    <Datetime>2011-06-17T01:00:00.000</Datetime>
    <Nvarchar>Hello, SQL Server!</Nvarchar>
</dbo04.ExcelTest>
<dbo04.ExcelTest>
    <ID>2</ID>
    <Nvarchar>Hello, Excel!</Nvarchar>
</dbo04.ExcelTest>
</doc>

Without :XML ON sqlcmd command the result is like:

XML_F52E2B61-18A1-11d1-B105-00805F49916B
---------------------------------------------------
0x440249004400440546006C006F00610074004408440061007...

(1 rows affected)

This is a reason why we cannot use the inline SQL query to export data to XML using sqlcmd.

However, we can use the bcp utility for inline SQL queries.

To top

SQL Server Data Export to XML using BCP

bcp is an SQL Server command line utility.

The basis export command for bcp:

bcp "SELECT * FROM dbo04.ExcelTest FOR XML AUTO, ELEMENTS, ROOT('doc')" queryout ExcelTest.xml -S. -dAzureDemo50 -T -c

where

-S.
Defines the localhost server. For a named instance you can use a parameter like .\SQLEXPRESS.
-dAzureDemo50
Defines the database AzureDemo50.
-T
Defines the trusted connection. Instead, you can use user credentials: -U Username -P Password
-c
Suppresses questions about text data types and uses character type.
ExcelTest.xml
Outputs the result to file ExcelTest.xml.

Note that most of the bcp and sqlcmd parameters are quite different. For example sqlcmd uses spaces for -S and -d parameters but bcp is not.

The result of the SQL query:

<doc>
<dbo04.ExcelTest>
    <ID>1</ID>
    <Float>1.234567000000000e+002</Float>
    <Datetime>2011-06-17T01:00:00</Datetime>
    <Nvarchar>Hello, SQL Server!</Nvarchar>
</dbo04.ExcelTest>
<dbo04.ExcelTest>
    <ID>2</ID>
    <Nvarchar>Hello, Excel!</Nvarchar>
</dbo04.ExcelTest>
</doc>

The result is different from the sqlcmd result in Float and Datetime value presentation but both are ok.

bcp cannot be used for data export to XML with input SQL query file like sqlcmd.

To top

SQL Server Data Export to XML Automation

We can use a simple batch file export-xml.cmd to automate data export:

@echo off

for /F "eol=; tokens=1* delims==" %%i in (config.txt) do set %%i=%%j

for /F "tokens=1* delims==" %%i in (task.txt) do call :RUN_ONE %%i "%%j"

goto END

:RUN_ONE

if not exist %2 goto RUN_BCP

:RUN_CMD

set Credentials=-U %Username% -P %Password%

if .%Username%.==.. set Credentials=-E

sqlcmd -S %Server% -d %Database% %Credentials% %Crypt% -s, -W -i %2 > %1

goto END

:RUN_BCP

set Credentials=-U %Username% -P %Password%

if .%Username%.==.. set Credentials=-T

bcp %2 queryout %1 -S%Server% -d%Database% %Credentials% -c

goto END

:END

The file uses server and credential definition from the config file config.txt like this:

; Leave Username and Password empty for trusted connection
; Leave Crypt empty for SQL Server without encryption
Server=ko7h266q17.database.windows.net
Database=AzureDemo50
Username=excel_user@ko7h266q17
Password=ExSQL_#02
Crypt=-N

Each pair of a resulting XML file and a source SQL query file or a source inline SQL query are defined at the text config file task.txt like this:

ExcelTest1.xml=ExcelTest.sql
ExcelTest2.xml=SELECT * FROM dbo04.ExcelTest FOR XML AUTO, ELEMENTS, ROOT('doc')

The batch file runs the bcp or sqlcmd utility depends on the source query type.

To top

Conclusion

The article contains a solution for SQL Server data export to XML using bcp/sqlcmd utilities.

You can use it to export SQL Server data to your Excel or OpenOffice users with fewer efforts.

Download

Downloads
Exporting SQL Server Data to XML Using BCP
Shows using the bcp utility for exporting SQL Server data to XML
Version: 1.7 | 10/23/2015 | 0.1MB | Article
Exporting SQL Server Data to CSV Using SQLCMD
Shows using the sqlcmd utility for exporting data to CSV
Version: 1.7 | 10/23/2015 | 0.1MB | Article
gsqlcmd (Portable Version) (Free)
Executes SQL queries and scripts, imports and exports data
Version: 3.4 | 11/02/2016 | 8.1MB | Homepage | Article 1 | Article 2
gwebcmd (Portable Version) (Free)
Converts XML, HTML, CSV, and JSON from the web and text files into CSV
Version: 3.3 | 11/02/2016 | 1.1MB | Homepage
SaveToDB Add-In for Microsoft Excel (Trial 30 days)
Allows using Microsoft Excel as a database client
Version: 6.10 | 11/02/2016 | 20.9MB | Homepage | Article 1 | Article 2

To top

Download

Comments  

# Bill Froelich 2012-08-30 14:35
Fantastic article! Thank you for the writeup. This is exactly what I was trying to do.
Reply | Reply with quote | Quote

Use Excel as a DB front-end
with the SaveToDB add-in

Use Excel as a DB front-end with the SaveToDB add-in