use xp_cmdshell. Check out the syntax in BOLe.g.declare @cmdxmlout varchar(1000)declare @ret int declare @tempfullpath varchar(500) set @tempfullpath = 'c:\test.xml'set @cmdxmlout='bcp "' + @sql + '" queryout '+ @tempfullpath +' -S(local) -T -c -r -t'exec @ret=master..xp_cmdshell @cmdxmlout,no_output
You may encounter problems getting the data back in however if you try and use BULK INSERT. It has a problem with files whose size is divisible by 4 if there are no field/line terminators (as there would be in an XML file) - bcp in works fine with a format file and you can use a generic one for all files. Here's an example of BULK INSERT going wrong - it drove me mad trying to figure it out during testing of exactly what you are trying to doFILE CONTENT1.txt 12.txt 123.txt 1234.txt 12345.txt 12345use tempdbgoif exists (select * from dbo.sysobjects where id = object_id(N'[testload]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [testload]gocreate table testload(filedata text NULL)goBULK INSERT testload FROM 'c:\load\1.txt' BULK INSERT testload FROM 'c:\load\2.txt' BULK INSERT testload FROM 'c:\load\3.txt' BULK INSERT testload FROM 'c:\load\4.txt' --divisible by 4 !!BULK INSERT testload FROM 'c:\load\5.txt'goselect * from testloadgoif exists (select * from dbo.sysobjects where id = object_id(N'[testload]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [testload]goResults :11212312345
I got so fed up with all this file stuff that I now just call a DTS package that uses ActiveX ScriptTask and XMLDOM to do the workIf you want any examples let me know. Getting XML out of SQL and then back in again in the same procedure is a pain.HTHJasper Smith