How to save multiple xml files using SQL Server Database -

i need create multiple xml files (per row) single query did. query generating 10,000 xml files know sql server assign different name each xml file need save them in c:\ automatically. can see files can’t save them advise or highly appreciated. 1 more hint: sql server can save each xml 1 one sql server file tab.

    create table dbo.sample(     [btno] [nvarchar](25) null,     [first_name] [nvarchar](35) null,     [last_name] [nvarchar](35) null,     [btid] [nvarchar](15) null, ) on [primary];  insert dbo.sample values('1b','vartan','sarkis','69876'); insert dbo.sample values('2b','anoush','eric','87656'); insert dbo.sample values('3b','lucine','arpiar','65467'); insert dbo.sample values('4b','anum','noor','98076'); insert dbo.sample values('5b','abercio','banninq','34897'); insert dbo.sample values('1c','gaea','nishan','29841'); insert dbo.sample values('7b','marilyn','vahe','78903'); insert dbo.sample values('2z','bansi','aakarshan','34905'); insert dbo.sample values('9s','eric','abban','45892'); insert dbo.sample values('12b','dave','tate','19994'); 

-- here query generates multiple xml files

select ((select * dbo.sample a.btid = b.btid          xml path('row'),type, root('bt')         )) dbo.sample b 

i cheated , used else's function actual write. note you'll need 'ole automation procedures' enabled write procedure work. company security policies little touchy these things being enabled. assume if isn't enabled, have ability so. write function overwrite files same filename, though may not relevant need.

declare @thexml xml declare @xmlstring varchar(max) declare @path varchar(255) declare @filename_start varchar(100) declare @filename varchar(100) declare @count int  set @path='c:\testdata\' set @filename_start = 'xmlname' set @count = 0  declare xcursor cursor select ((select * dbo.sample a.btid = b.btid          xml path('row'),type, root('bt')         )) dbo.sample b  open xcursor  fetch xcursor @thexml  while @@fetch_status <> -1 begin   set @count = @count + 1   set @filename = @filename_start + cast(@count varchar) + '.xml'   set @xmlstring = cast(@thexml varchar(max))   exec master.dbo.spwritestringtofile @xmlstring,@path,@filename    fetch xcursor @thexml end  deallocate xcursor 

enabling ole - http://msdn.microsoft.com/en-us/library/ms191188.aspx

credit write procedure - https://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/

actual write procedure (must change alter create) - https://www.simple-talk.com/code/workingwithfiles/spwritestringtofile.txt