I am trying to write a text file and I have 2 issues.
1) I am currently using this code as a sample exec master..xp_cmdshell 'echo XML_FILE > c:\file.txt'. It works fine, however I am trying to write a custom XML file that is being used by a service that reads the file. So the output needs to include < and >. So how can I have this written as an output? < XML_FILE >
2) I need the path to be a mapped drive. When I do the code to the c: it works fine, but when I change it to my mapped drive p: it fails with The system cannot find the path specified.
Any help would be appreciated!
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:
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:
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.
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?
answered Sep 05, 2012 at 03:18 PM
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]
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. : http://www.robvanderwoude.com/escapechars.php
answered Sep 05, 2012 at 03:54 PM
I think you want to create al file in xml format that contain a query result, if it is the case, you should try:
The second, verify that the user that runs SQL services have access to the remote folder.
answered Sep 08, 2012 at 12:58 AM