How to write a text file

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!

more ▼

asked Sep 04, 2012 at 03:10 PM in Default

jclyma gravatar image

0 1 1 2

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.
more ▼

answered Sep 04, 2012 at 09:37 PM

KenJ gravatar image

20.3k 1 4 12

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Sep 05, 2012 at 03:18 PM

jclyma gravatar image

0 1 1 2

(comments are locked)
10|1200 characters needed characters left


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
more ▼

answered Sep 05, 2012 at 03:54 PM

TimothyAWiseman gravatar image

15.6k 20 23 32

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Sep 08, 2012 at 12:58 AM

JD gravatar image

104 6 7 8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 04, 2012 at 03:10 PM

Seen: 1998 times

Last Updated: Sep 08, 2012 at 12:58 AM