Export SQL to XML using bcp with where condition

Question

I try to export data to XML with this code, it's normally working fine:

declare @cmd nvarchar(255);
select @cmd = 'bcp "SELECT * from [db].[dbo].[TW_StockReport](0,25,26,4,29,30,19,31) row For XML auto, XMLSCHEMA, root(''node'')" ' +
    'queryout "D:\Temp\dbstockandsalereport.xml" -S -T -w -r -t';

exec xp_cmdshell @cmd;

but when I try to add a WHERE condition, it's no longer working:

declare @cmd1 nvarchar(1155);

select @cmd1 = 'bcp "SELECT INVENTLOCATIONID, AgeOfItem AS ''ProductYear'', ISNULL(SUM(Qty), 0) AS ''Unit'', COUNT(c.ItemID) AS ''ProductType''
FROM [db].[dbo].[TW_ItemsNonMovement] c 
LEFT JOIN [db].[dbo].[TempInventTable] d ON c.ITEMID = d.ITEMID collate Thai_CI_AS
WHERE d.TW_DEPARTMENTID = ''PMMan'' AND DateDiff <= 120 AND Qty > 0 
GROUP BY INVENTLOCATIONID, AgeOfItem ORDER BY INVENTLOCATIONID, AgeOfItem row FOR XML AUTO, XMLSCHEMA, ROOT(''node'') ;"' + 
'queryout "D:\Temp\PMMan-120.xml" -S -T -w -r -t';

exec xp_cmdshell @cmd1;

Answer 1

It is better to create a stored procedure (SP) and put there your entire SELECT statement. After that just call that stored procedure in bcp.

Additionally. this way you can always test the SP on its own and make sure it does what is expected of it.

So it will be like follows:

bcp "EXEC yourStoredProcedure;" ...