Create Text File without overwriting 1

[Tinkerers]

Hi,

I need to create an xml file. First I write some initial xml to the file:
--Create an xml file named cmts_xml.xml
exec master..xp_cmdshell 'echo ^<?xml version="1.0" ?^> > f\projects\cmts_xml\cmts_xml.xml'
exec master..xp_cmdshell 'echo ^<request^> >> f\projects\cmts_xml\cmts_xml.xml'
exec master..xp_cmdshell 'echo ^<query app_key="AUDIT_TEST"^> >> F\Projects\cmts_xml\cmts_xml.xml'
exec master..xp_cmdshell 'echo ^<device-list^> >> F\Projects\cmts_xml\cmts_xml.xml'


Then here's where I get in trouble. This next command works great, BUT it overwrites my previous entries in the file.
exec master..xp_cmdshell 'bcp "cdvsupport.dbo.VW_CMTS_XML" out F\Projects\cmts_xml\cmts_xml.xml -c -SMachelmc01 -Uuser -Ppassword'
exec master..xp_cmdshell 'echo ^</device-list^> >> F\Projects\cmts_xml\cmts_xml.xml'
exec master..xp_cmdshell 'echo ^</query^> >> F\Projects\cmts_xml\cmts_xml.xml'
exec master..xp_cmdshell 'echo ^</request^> >> f\projects\cmts_xml\cmts_xml.xml'

How can I rewrite this so when the result set is written to the file, it doesn't overwrite what's already there ?

Thanks for any help,
Paul

 

[AlexCuse]

I think by default bcp overwrites the file contents. You can use bcp to export query results, right? If so, why not union your header to the top and write it out all at once?

Here's some more reading on BCP.

http://msdn2.microsoft.com/en-us/library/ms162802.aspx

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

Or if you like, put the two to different files, then use

copy sourcefile1 + sourcefile2 destfile

destfile can be the same as sourcefile1.

It can't be the same as sourcefile2 or the copy doesn't work. In the past the command interpreter would say"contents of destination lost before copy" or something like that. But I just tried it now and it asks you if you want to overwrite, still failing to join the two files, so avoid using sourcefile2 as destfile.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]