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