Writing contents of XML Variable to File

Question

The code below creates me an XML file that shows up as a link as shown in image1

DECLARE @xmlentire AS XML = (
select (select @xmlheader as returnXml),
(select @xmlbody as returnXml)
for xml path ('IndirectSalesMessage'))

select @xmlentire
SELECT @loop = @loop + 1

the contents that are returned to me are great. When I click on that link I get everything I need. The problem is that I want to automatically export these contents to an XML file. Right now I have to click on the link and then save the file as an XML file manually. I want a program to do that automatically. I tried BCP already but BCP is not allowing me to work with XML Variables.

The problem is that this program should loop through between 200-250 times to create a file each time through. saving this manually for each run is not feasible. I need a command right before the SELECT @loop = @loop + 1 that will save the contents of @xmlentire to an xml file and then move to the next file. Can someone please help?

Image 2 shows how the file looks after saving manually. Everything I need is perfect. I simple need to save @xmlentire as an xml file automatically.

Answer 1

A command like this should write a query's result into a file. Beware of the -w option, as XML is unicode (UCS-2) always...

DECLARE @FileName NVARCHAR(250)='C:\SomeDir\SomeFile.xml'; 
SET @cmd = 'bcp  "select * from master.sys.types FOR XML PATH(''Type''),ROOT(''root'')" ' + 
           'queryout "' +  @FileName + '" -w -T -S ' + @@SERVERNAME
EXEC master..XP_CMDSHELL @cmd;

You can set the filename to the variable first.

My suggestion:

Create a table and write all your XMLs together with the appropriate filename into it. Then use a CURSOR (one of the rare situations where this is a good idea) and write them out. This allows you to keep all XMLs in a staging table and do the export whenever this fits. You can set an IsWritten flag and store error messages if something goes wrong...