Getting blank XML file using BCP with queryout

  • Brian Pastre

    SSC-Addicted

    Points: 494

    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.

  • Site Owners

    SSC Guru

    Points: 80376

    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: 996622

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Brian Pastre

    SSC-Addicted

    Points: 494

    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: 996622

    Brian Pastre wrote:

    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?

    Yes.  You'll also need to identify that you want to use "trusted" logins so that you don't have to hard code a login and password.  You might want to check on the other "switches" you invoked... they might not be necessary if your just trying to blob out some XML.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

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

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