SQL Server Export to Excel using bcp/sqlcmd Utilities and XML

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.

Table of Contents

• Introduction
• SQL Server Data Export to XML using SQLCMD
• SQL Server Data Export to XML using BCP
• SQL Server Data Export to XML Automation
• Conclusion
• Download
• See Also

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.

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.

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.

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