Search code examples
sqlsql-serverxmlt-sqlbcp

Strange XML-Export behaviour

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

Solution

  • 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