How to prevent sqlcmd truncation with for xml path query result
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.
You need to output your XML as XML type. Normally, that is done using TYPE directive in addition to FOR XML. But if you want to add the Xml declaration, you will have to put everything into an xml variable then select that. Ex:
declare @xml as xmlselect @xml = cast(('<?xml version="1.0" encoding="UTF-16"?>'+cast((SELECT Columns FROM Table FOR XML PATH ('Product'), ROOT('Products')) as nvarchar(max))) as xml)select @xmlEdit: Sorry. Adding Xml declaration will not work because Sql presents the stream as XML in its native encoding (UTF-16) and it will strip off any declaration.
Edit: