exec sp_configure 'show advanced options', 1; reconfigure; exec sp_configure 'xp_cmdshell', 1; reconfigure;
用sql写个作业每隔五分钟去读某个文件夹下所有xml文件，并把它的数据插入到一张表在把它删除
--create table TEST ( --A char(10),B char(10),C char(10) --) DECLARE @XMLDIR varchar(2000), --XML放置的文件全路径 @DIR varchar(1000), --XML的文件夹 @FileFullName varchar(8000) --游标用,文件名称(全路径) declare @FileTable TABLE(x varchar(8000)) --临时表,存储 文件夹所有XML文件名称 SET @DIR='D:\TEST' SET @XMLDIR=N'DIR '+ @DIR + '\*.xml' --*.xml 只读取是XML的扩展名文件 INSERT @FileTable exec xp_cmdshell @XMLDIR --将文件夹的内容读取插入临时表中 delete from @FileTable where x not like '%.xml%' or x is null update @FileTable set x=@DIR+'\'+SUBSTRING(x,37,120) --select * from @FileTable declare fCursor cursor for select x from @FileTable open fCursor fetch next from fCursor into @FileFullName while(@@fetch_status=0) BEGIN declare @xml varchar(8000), --XML转换成列的内容 @Pointer INT, --指向位置的变量 @DELFILENAME varchar(2000) --删除文件的CMDSHELL declare @table TABLE(x varchar(8000)) SET @DELFILENAME= 'DEL '+@FileFullName insert into @table EXEC ('(SELECT * FROM OPENROWSET(BULK '''+@FileFullName+''',SINGLE_CLOB) as x)') select @xml=x from @table EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@xml INSERT INTO TEST(A,B,C) SELECT A,B,C FROM OPENXML (@Pointer,'ROOT/Row')WITH(A varchar(10),B varchar(10),C varchar(10)) EXEC sp_xml_removedocument @Pointer EXEC xp_cmdshell @DELFILENAME fetch next from fCursor into @FileFullName END close fCursor --关闭游标 deallocate fCursor --删除游标
剩下的就是加个代理了..哈...5分钟执行一次
最新评论