Job to write query data to an XML file

Question

I need to save the result of a query in an XML through a job that will run scheduled daily in SQL Server. Procedure already brings the result in XML format with tags, in a variable. I just need to play the contents of this variable in XML. What is the best way to generate this XML? How I define the path that XML will be saved and how I define the file name?

Thanks!

Answer 1

Well, it’s basically using the command xp_cmdshell:

DECLARE @command VARCHAR(8000) = 'echo ' + @xml + ' > c:\meus\diretorios\teste.txt';

EXEC xp_cmdshell @command;