You may have a need to append some text to a file. Suppose you have an existing file C:\test\myfile.txt. You want to add hello world to the file.Currently the file is empty. After that you want to add another line of text how are you?. Open a command prompt and navigate to the directory that contains your file. Below is a session at the command prompt.
C:\>cd test C:\test>echo hello world >> myfile.txt C:\test>echo how are you? >> myfile.txt C:\test>echo ^<?xml version="1.0"?^> >> myfile.txt C:\test>type myfile.txt hello world how are you? <?xml version="1.0"?> C:\test>
DOS Escape Characters
The only way to add the third line (the xml prolog) is to use the escape character ^. The less than and greater than characters are redirection commands for DOS. You can override that interpretation by escaping those characters. You can search on the Internet for DOS Escape Characters for more information or just go to Rob van der Woude’s page.
Knowing a little bit about the command line comes in handy when you are working in stored procedures in SQL Server and you need to export data to files. In SQL Server you can use xp_cmdshell. For some information on how to run OS commands from SQL Server have a look at the post called SQL Server xp_cmdshell
Merging Files from within SQL Server
The conclusion is: only type works, not copy, when merging a text file with an xml file. Let me set the stage. Suppose you have an XML document that you have successfully exported into a file called temp.xml. However, the computer system that you are sending the file to requires a header line that identifies the file type. Suppose that is something like CODE52983. That data is stored in a second file called mydata.xml. From withing SQL Server you need to merge the two files. Use the DOS command type and the append >>. You want to put the xml data under the header line in the mydata.xml file. Here is some sample code.
DECLARE @MergeString VARCHAR(200); SET @MergeString = 'type temp.xml >> mydata.xml'; EXEC xp_cmdshell @MergeString;