I export a column of type image to files using this command:

EXEC master..xp_cmdshell 
'BCP "SELECT file_name FROM file_table WHERE id = 11 " 
queryout "C:\bcpdir\test.xml" -T -N'

In SQL Management Studio, I can see the hex value of the field starts with 0x3C3F786D6C which is correct for an XML file (<?xml).

However, the command above seem to add an 4 byte header to the file. How can I use BCP without this header?

  • This describe the same problem: social.msdn.microsoft.com/Forums/sqlserver/en-US/…
    – Jonas
    Oct 24 '14 at 9:01
  • By not using -N and by executing the BCP command from commandline, I get the option to set prefix to 0 instead of 8. But I would like to be able to do it from the EXEC command.
    – Jonas
    Oct 24 '14 at 9:02

The solution seem to be to first execute BCP manually, set prefix to 0 in the user dialog. And then use the generated .fmt-file as in this command:

EXEC master..xp_cmdshell 'BCP "SELECT my_column FROM my_table WHERE my_id = 11 " 
queryout "C:\bcpdir\bcpout.xml" -T -f "C:\bcpdir\bcp.fmt'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.