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 (stackoverflow.com/questions/10894688/…) to read the data. The behavior seems to be a known bug in sql 2012, see link for workarounds connect.microsoft.com/SQLServer/feedback/details/786004/… — Frode F.
– Frode F., Commented Mar 14, 2017 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
– David, Commented Mar 14, 2017 at 19:11

Community · Accepted Answer · 2017-05-23 12:02:59Z

5

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.)

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Mar 14, 2017 at 19:32

David

221k42 gold badges245 silver badges337 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 10:30:52Z

4

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/

edited May 23, 2017 at 10:30

CommunityBot

11 silver badge

answered Mar 14, 2017 at 19:23

Frode F.

55.4k9 gold badges104 silver badges118 bronze badges

Comments

KeyMaker00 · Accepted Answer · 2018-08-14 16:45:21Z

1

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.

edited Aug 14, 2018 at 16:45

KeyMaker00

6,5222 gold badges56 silver badges50 bronze badges

answered Aug 14, 2018 at 14:19

user10225069

111 bronze badge

Collectives™ on Stack Overflow

sqlcmd and/or PowerShell wrapping XML output

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related