1

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

Result when select @xmlentire is run

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.

enter image description here

Long Luong
  • 764
  • 2
  • 14
  • 28
jp3nyc
  • 71
  • 10
  • One - important! - hint: If this under your control avoid culture dependant date formats like in ``. A date like `01/02/2017` can be taken as first of Feb or as second of Jan. With `11/27/2017` it might lead to hardly findable errors. Always use ISO8601 like in `` . – Shnugo Dec 31 '17 at 12:06

1 Answers1

0

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

Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Thank you so much sir. But I do have to ask. The "select @xmlentire" is essentially selecting and displaying the contents of an XML variable. This might be different than the ""select * from master.sys.types FOR XML PATH(''Type''),ROOT(''root'')"" that you have displayed above. Do you think it will work for this case? – jp3nyc Dec 31 '17 at 11:30
  • @jp3nyc Just try it... The `queryout` switch is used to send the result of a query directly to the given filename. Your `SELECT @xmlentire` would not work within `BCP command`, the variable is not known there. But - if you follow my suggestion with the staging table - it should be easy to write all collected XMLs to files in one `CURSOR` loop. Beware to fully qualify the table's name like `SELECT SomeColumn FROM SomeDatabase.dbo.TableName`. – Shnugo Dec 31 '17 at 11:40