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.

Saprogenic

If you're just working in SSMS, then there are options to control how much XML data can be returned. 1MB, 2MB, 5MB or Unlimited. See Tools | Options | Query Results | SQL Server | Results to Grid | XML data. It doesn't have an option for "Results to File". But perhaps you could run the query to the Grid, and then save the results? – Fastigiate

If you use SSMS only then that would work, but I need to automate the process with a batch file. The SSMS settings don't have effect on the sqlcmd outcome. Thanks anyway! – Saprogenic

I've used BCP to do things like this. Here's a BCP example: https://mcmap.net/q/914532/-how-do-i-use-bcp-or-sql-server-management-studio-to-get-blob-data-out-of-sql-server – Fastigiate

Yes that's also a possibilty, I tried BCP, didn't work, but just found out xp_cmdshell is not active, see what it does if I fix that. I will try with SSIS package also. excel-sql-server.com/… – Saprogenic

After exhausting all visible options to sqlcmd.exe and SSMS, I wrote a Java program to extract the XML column I needed. Hardly a universal solution, but it got this job done. – Derman

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.

Radish

-y0's maximum is one megabyte. (I've got the same problem as OP where 1048676 characters are not enough.) – Derman

When I tried using -h-1 and -y0 I got this error message: Sqlcmd: The -h and the -y 0 options are mutually exclusive.. Although it seems like even without -h-1 the header is excluded anyways. – Watersick

Answer 2

Using -y0 by itself opened up the widest column so far to 664,241. I don't know what the limit is, but this seems quite good. The -h-1 is for no headers.

sqlcmd -S XXXXX -E -o test.txt -i SelectStmt.spl -h-1 -y0

The SelectStmt.spl has 40 fields and one field is a binary picture which is why one column width is so wide.

Deltoid

Answer 3

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 xml
select @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 @xml

Edit: 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:

Debatable