How to write a text file

1) I'm a little fuzzy on how you are generating the xml file but, to put the literal string you supplied into an xml file using xp_cmdshell, you might try something like this:

EXEC xp_cmdshell 'bcp "SELECT ''< XML_FILE >''" queryout "c:\file.txt" -S YourServer\Instance -d tempdb -T -c'

If you want to output the results of a query to the file, you can be a little more flexible by substituting your query into something like this:

EXEC xp_cmdshell 'bcp "SELECT * FROM sys.tables FOR XML AUTO" queryout "C:\file.txt" -S YourServer\Instance -d tempdb -T -c'

2) Your SQL Server is running under a different login than the one you use. That login does not have your mapped drive. Try to use a UNC path.

I think you want to create al file in xml format that contain a query result, if it is the case, you should try:

EXEC xp_cmdshell 'bcp "your_query FOR XML AUTO" queryout "C:\file.txt" -S SQLserver\Instance -T -c'

The second, verify that the user that runs SQL services have access to the remote folder.

Whether it is proper or not this is what I am trying to do. I need to write an XML file from my clients web site (Web Server is in DMZ) and drop it on a desktop computer (in the LAN) for a program to authorize a payment. Previous solutions failed due to communication between the two machines. So I am using exec master..xp_cmdshell to write the XML file to the desktop. (by the way the UNC path fixed issue 2) None of the data resides in the database, it is all entered by the user from the web form. So writing this line by line works for me assuming I can get the < and > to appear. I am assuming they are not showing because of how they are a part of the echo syntax. This is the line of code I am running: exec master..xp_cmdshell 'echo XML_FILE > c:file.txt' and this is the result in the text file: XML_FILE

What I need it to show is: < XML_FILE >

Does this clarify at all?

Thanks, John

jclyma

To directly answer your question, you are right that < and > will have problems with echo and most other dos commands because they are interpreted by the dos interpreter. They can be escaped by escaped by placing them inside of double quotes " . There is a good explanation of this at : [http://www.robvanderwoude.com/escapechars.php][1]

Now, with that said doing this inside of SQL Server is probably a sub-optimal approach even when the data does reside in the database, and is probably a really sub-optimal approach when the data is not coming from a database in the first place. If I were in your position, I would have the code for the website itself create the XML file. If that is not an option, then I would consider a more traditional languate like C# or Python to handle this task. While T-SQL can do this (especially in conjunction with with xp_cmdshell), T-SQL is a domain specific language highly tailored to deal with databases and it becomes awkward when stepping outside of that domain.

Just as a side note, its also possible to write a text file using opendatasource and avoid xp_cmdshell and Dos, though that also is awkward, especially when you are not trying to write something like a CSV text file which is dumping the contentes of a table. [1]: http://www.robvanderwoude.com/escapechars.php