I'm trying to export a query to an XML file. The file gets created fine but it's empty so it's my Select query. I've tried setting quoted_identifiers to on and off but am still get a empty output file. Here's my query:
EXEC xp_cmdshell 'bcp "SELECT * FROM (Select top 100 CSAT Comments as ''Category'', d.Date_Entered_UTC as Date, Rating=CASE WHEN Total_Points =0 THEN ''Okay'' WHEN Total_Points <0 THEN ''Bad'' ELSE ''Good'' END, Open_Ended_Response as Comment FROM SV_ResultsDtl as d INNER JOIN SV_ResultsHdr as h on h.SV_ResultsHdr_RecID=d.SV_ResultsHdr_RecID WHERE Open_Ended_Response <> '' ORDER BY d.Date_Entered_UTC DESC) AS DATA FOR XML PATH(''Record''), ROOT(''MarketingData'')" queryout "C:\Temp\bcptest.xml" -T -c -t,'
This runs fine in SSMS.