Getting blank XML file using BCP with queryout

Question

Post reply

Getting blank XML file using BCP with queryout

Brian Pastre

SSC-Addicted

Points: 494
More actions
May 18, 2020 at 9:58 pm

#3752723

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80325 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Brian Pastre wrote:

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.

What do you mean that "This runs fine in SSMS." and where are you trying to run it from where it doesn't work? Do you mean the SELECT works fine in SSMS but the BCP doesn't?

If that's the case, do you see anywhere where you identify which database or server to use in the BCP command? That's a hint. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Brian Pastre SSC-Addicted Points: 494 More actions · Answer 3

Yes, the Select runs fine but the BCP doesn't. I'm running the BCP in Query Analyzer in SSMS on the server and database the Select is run on. So I still need to identify the server and database?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4