EtienneK

I needed to construct a dynamic HTML document using Transact-SQL the other day. I googled a bit and found that the easiest way to write to a text file in Transact-SQL is to call the command prompt echo command. The problem, though, was that it’s not possible to echo the ‘<’ and ‘>’ characters with the echo command. Well, that’s not 100% true. To be more precise, it’s not possible to do something like the following:

echo <b>Hello World</b> > hello.html

Command prompt doesn’t like you using the ‘<’ and ‘>’ characters, because it uses these characters to redirect the standard input and output to other sources (like our hello.html file in the example, above).

This is how to make it work:

echo ^<b^>Hello World^</b^> > hello.html

Wasn’t that easy? Just use the ‘^’ escape character in front of the ‘<’ and ‘>’ characters when echoing in the command prompt. Obviously, we don’t want to escape the last ‘>’ character, because we want to send our string, outputted by echo, to hello.html.

And this is how it’ll look in Transact-SQL:

exec master..xp_cmdshell 'echo ^<b^>Hello World^</b^> > hello.html'

Easy.

As a final note, the ‘^’ escape character only works in the command prompt of the Windows NT family of operating systems.