Strange XML-Export behaviour

Question

declare @cmd nvarchar(255) 

SET @cmd = 'bcp "select * from Testdb.dbo.mytable WHERE nr LIKE ''%102065336''' + '" queryout C:\temp\sample.xml -c -t, -S' +  @@servername +  ' -T'

exec xp_cmdshell @cmd

That Code returns a correct .xml file with correct format, but when i use this @cmd so with "=" instead of "LIKE" the xml file looks broken(only cryptic chars in it) :

SET @cmd = 'bcp "select * from Testdb.dbo.mytable WHERE nr = ''102065336''' + '" queryout C:\temp\sample.xml -c -t, -S' +  @@servername +  ' -T'

So How is this possible? The queries return the same data if i execute the sql statement...

score 1 · Accepted Answer · edited May 23 '17 at 11:33

I cannot reproduce this. Check it out:

USE master;
GO
CREATE DATABASE tstDB;
GO
USE tstDB;

CREATE TABLE mytable(nr VARCHAR(100));
GO
INSERT INTO mytable VALUES('11'),('12'),('22');
GO

declare @cmd nvarchar(255); 

SET @cmd = 'bcp "select * from tstDB.dbo.mytable WHERE nr LIKE ''%11'' FOR XML AUTO' + '" queryout C:\temp\sample1.xml -c -t, -S' +  @@servername +  ' -T';

exec xp_cmdshell @cmd;

SET @cmd = 'bcp "select * from tstDB.dbo.mytable WHERE nr = ''11'' FOR XML AUTO' + '" queryout C:\temp\sample2.xml -c -t, -S' +  @@servername +  ' -T';

exec xp_cmdshell @cmd;
GO
USE master;
GO
--careful with real data!
DROP DATABASE tstDB;
GO

Some ideas:

You speak about XML export, but the code you show does not create any XML? So maybe the issue is in an area we cannot see...
You declare your command with a size of 255. This is pretty small... Might be, that something is truncated
generall hint: Use -w instead of -c when you export XML. Find details here

Strange XML-Export behaviour

1 Answers1