sqlcmd and/or PowerShell wrapping XML output

Question

I'm executing the following command:

sqlcmd -i "\\path\to\sqlfile.sql" -S "ServerName" -d "DBName" -y 0

Wherein sqlfile.sql is a handful of DECLARE statements for variables and then a tree of nested SELECT ... FOR XML Path('...') statements to ultimately generate a big XML string as output.

In a PowerShell command prompt, I pipe the output to a file:

sqlcmd -i "\\path\to\sqlfile.sql" -S "ServerName" -d "DBName" -y 0 | Out-File C:\path\to\output.xml

(Or I could use -o on the command itself, I have no strong preference either way.)

The goal here is to have a single XML file that can then be loaded into another downstream process. This works well, save for one detail that currently has me stuck. The output (to the file) is auto-wrapped at 2,033 characters. Which means that it generates broken XML if (as in most cases) the wrapping breaks a tag:

<SomeTag>This is some content just to illustrate.</Som
eTag>

I've tried using -w on the sqlcmd arguments. In testing that would successfully wrap at values lower than 2,033, but at any value higher than 2,033 (20000 for example, which would be more than wide enough) that same maximum of 2,033 would remain.

I also tried calling -replace before piping the output to try to remove line breaks, such as:

([the sqlcmd command above]) -replace "\r", "" | Out-File C:\path\to\output.xml

To include attempts on \r, \n, \r\n, and using back-ticks instead of back-slashes. All to no avail. (In testing, -replace "KnownString", "TEST" did successfully replace the known string with TEST.)

Perhaps tunnel-vision has taken over here. Am I missing something easier? The goal is to have a PowerShell command-line command which executes a .sql file and puts valid XML (doesn't need to be pretty-printed, although that would be cool) into an .xml file.

Is there something I can add/modify here which would break out of that 2,033-character width limit? Or perhaps a more creative approach to achieve the same goal?

Tried `-repalce '\r|\n'` to make it handle either order of line feed/carriage return? You could also use an older version of `sqlcmd`, use native `SqlClient`-classes in poweshell or cmdlets (http://stackoverflow.com/questions/10894688/how-to-execute-sql-file-using-powershell) to read the data. The behavior seems to be a known bug in sql 2012, see link for workarounds https://connect.microsoft.com/SQLServer/feedback/details/786004/sqlcmd-with-xml-on-break-lines-on-large-output — Frode F., Mar 14 '17 at 19:01
@FrodeF.: It looks like a combination of those workarounds is doing the trick. Specifically, wrapping the `SELECT` in an `XML` type variable and then selecting that variable as `NVARCHAR(MAX)`. Works with `sqlcmd`, and no character replacement needed. Feel free to add as an answer, thanks! — David, Mar 14 '17 at 19:11

score 4 · Answer 1 · edited May 23 '17 at 10:30

Some suggestions:

Try -replace '\r|\n' to make it handle either order of line feed/carriage return
You could also use an older version of sqlcmd, use native SqlClient-classes in powershell or the SQL-server module to read the data (ex. How to execute .sql file using powershell?).

The behavior seems to be a known bug in sqlcmd 2012, see bug report on connect for other workarounds: https://connect.microsoft.com/SQLServer/feedback/details/786004/

score 4 · Accepted Answer · edited May 23 '17 at 12:02

Following up on the other answer...

(Note: I have to stay specific to SQL 2012 in this case. It's a client-owned workstation and they have their rules.)

I wrapped my .sql file in the following:

SET NOCOUNT ON
DECLARE @XML AS XML

SET @XML = ([the great big set of nested selects generating xml])

SELECT CAST(@XML AS NVARCHAR(MAX))

Then invoked simply as:

sqlcmd -i "\\path\to\sqlcode.sql" -S "ServerName" -d "DBName" -o "C:\path\to\output.xml" -y0

The result is a single line of XML in the output file. (I can pretty-print it for kicks later.)

score 1 · Answer 3 · edited Aug 14 '18 at 16:45

I had a similar issue exporting SQL queries as JSON. Every 2033 characters the output would start a new line using either sqlcmd or Invoke-SQLcmd. Setting the output to an nvarchar forced the content onto a single line.

SET NOCOUNT ON
DECLARE @JSONOUTPUT AS NVARCHAR(MAX)

SET @JSONOUTPUT = ([SELECT * .....])

SELECT @JSONOUTPUT

I called this using Invoke-SQLcmd $sqlquery -maxcharlength 10000 | select -expandproperty Column1 but could be done with cmdsql as well.

sqlcmd and/or PowerShell wrapping XML output

3 Answers3