Hey Yo! SQLCMD may truncate your XML!
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