BCP Command for XML Export

• nathans 30006

  SSC Enthusiast

  Points: 115

  More actions

  November 4, 2013 at 7:38 pm

  #395775

  Hi All,

  I'm trying to run a query to export one of the tables currently sitting in our SQL Server using the following command:

  DECLARE @FileName varchar(50),

  @bcpCommand varchar(8000)

  SET @FileName = 'D:\SQLextracts\OWNER_CDG.SBA_SUBAGENTACCOUNTING'+'.xml'

  SET @bcpCommand = 'bcp "SELECT * FROM [database].[Data Table] FOR XML AUTO, ROOT("[Data Table]''), ELEMENTS" QUERYOUT "'

  SET @bcpCommand = @bcpCommand + @FileName + '" -T -c -r -t'

  PRINT @bcpCommand

  EXEC master..xp_cmdshell @bcpCommand , No_output

  go

  This works for all the other tables I'm extracting, however for this one the output is in .XM format and not .XML .

  Any ideas as to why this is occurring and how I need to go and fix the issue? If more detail is needed let me know.

  Thanks.

• PatPhelan

  SSC Rookie

  Points: 25

  More actions

  November 4, 2013 at 9:12 pm

  #1664025

  Your filename is 51 characters long. You declare the variable to be 50 characters long. Truncation occurs.

  -PatP

• nathans 30006

  SSC Enthusiast

  Points: 115

  More actions

  November 4, 2013 at 9:46 pm

  #1664027

  Brilliant, it worked!

  Thanks heaps for the help and the quick response.

  Nathan