Welcome to Julian Kuiters Saturday, August 18 2018 @ 05:12 PM AEST

Hey Yo! SQLCMD may truncate your XML!

  • Contributed by:
SQL Server 2005

While trying to use SQLCMD to produce an app.config file, I encountered problems with the output being truncated.

The script was similar to:

DECLARE @appconfig XML
SET @appconfig = '<configuration></configuration>'
... Insert configuration nodes
-- Output the config
SELECT @appconfig as ApplicationConfiguration

The default options for SQLCMD produced output like:

ApplicationConfiguration
-------------------------
<configuration><connecti

(the value was a little longer, but cut off at the end)

Clearly the xml was not valid. There's header text included, corrupted tags, and missing closing tags. So I hunted through books online and google for an answer.

Turns out I needed to use the -y 0 option, which will output xml columns up to 1MB in size.

Also the -h-1 (without any spaces) will remove the header from the output.

If you are planning to use SQLCMD to output XML, remember: Hey Yo! -h-1 -y 0!

sqlcmd -h-1 -y 0 ...
<configuration><connection/><configuration>

Read more on SQLCMD Utility Options