How to prevent sqlcmd truncation with for xml path query result

Question

After reading one answer and second answer and the infopage on sqlcmd I still cannot get the following to work.

I am trying to query a result into a xml file using a sqlcmd in batch file.

The batchfile looks like this:

sqlcmd -R -d DBName -i "c:\inputquery.sql" -h-1 -y 0 -o "c:\outputfile.xml"

The simplicated sql query is:

:XML ON
SELECT '<?xml version="1.0" encoding="UTF-8"?>' +
CAST((
SELECT Columns FROM Table
FOR XML PATH ('Product'), ROOT('Products')
) 
AS NVARCHAR(MAX))

The output is a xml file of about 1025Kb whit a truncated string. I think it truncates to 1mb, but how can you prevent this? The goal is to get the full query result into the xml file. As far as I know, all options are used already. Using TSQL SSMS2008 by the way.

Answer 1

I ran into the same issue today, I used the -y0 option. My output looks correct now. Thought I would post my findings so it might help others running into the same thing.

sqlcmd -Sxxxxxxx -E -dmaster -h-1 -y0 -Q" my query that produces long results in here;" > "D\:out.txt"

-h-1 removes the column headers too.