Suppressing SQL Server Management Studio's 'Messages' from output of SQLCMD via SQL Server Agent

I've created a script that outputs data in an XML format, and was using SQL Server Agent (Job Step of Type 'Operating System (cmdexec)') to run a job monthly/quarterly to output the data to a specific file. However, the issue that we are running into is that the output from the SQLCMD script:

sqlcmd -d -i "\.sql" -o "\.xml" -y 0

is outputting the 'Messages' as well as the 'Results'. Is there a way to only write the results via sqlcmd?

I've removed a few messages including:

"Warning: Null value is eliminated by an aggregate or other SET operation.
"

"(1 row affected)"

By using the following Code:

SET NOCOUNT ON
SET ANSI_WARNINGS OFF
GO

Thanks for your suggestions!

A better approach is to switch over to a PowerShell job step, which is about 1000% more powerful than CMDEXEC. For instance in PowerShell writing an XML (or JSON) result to a file is as easy as:

invoke-sqlcmd 'select * from sys.objects for xml path' | % { $_[0] } | out-file 'c:\temp\results.xml'

Since PowerShell passes results as streams of objects, cmdlets can differentiate between data and messages, and only put data on the output pipeline. eg this

invoke-sqlcmd "print 'hello'; create table #T(id int); insert into #t values (12); select * from sys.objects for xml path" -Verbose | % { $_[0] } | out-file 'c:\temp\results.xml'

Still creates a valid XML file, as only the rows that result from the SELECT are sent through the pipeline to out-file.

By contrast sqlcmd.exe is a separate program and can only pass results to the calling program through Standard Output and Standard Error, which are just plain text streams. So it can't differentiate between errors, informational messages, resultsets, and rowcount messages.

StackExchange Database Administrators Q#305519, answer score: 10