通用的存储,来导入指定目录下的所有xml文档
create proc p_inport
@path nvarchar(1000)='d:\xml', --xml文件所在的目录
@fname nvarchar(1000)='' --要导入的xml文件名,如果不指定,表示只导入今天的,如果为:%,则导入该目录下所有的文件,如果是其他,表示只导入指定的
as
DECLARE @idoc int,@doc varchar(8000)
if right(@path,1)<>'\' set @path=@path+'\'
if @fname='' set @fname=convert(char(8),getdate(),112)+'.xml'
--得到指定目录下的所有文件:
create table #t(fname varchar(260),depth int,isf bit)
insert into #t exec master..xp_dirtree @path,1,1
--定义处理的文件
declare tb cursor local for
select fn=@path+fname from #t
where isf=1 and fname like @fname
--导入处理
declare @fn varchar(2000)
create table #tb (doc varchar(8000))
open tb
fetch next from tb into @fn
while @@fetch_status=0
begin
exec('bulk insert #tb from '''+@fn+'''')
set @doc=''
select @doc=@doc+doc from #tb
--分析XML,导入表中
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
insert table1
SELECT * FROM OPENXML (@idoc, '/Database/Vehicle',1)
with table1
insert into table2
SELECT * FROM OPENXML (@idoc, '/Database/Vehicle/Pictures/Picture',1)
with(
ExamineNumber Char(30) '/Database/Vehicle/@ExamineNumber',
PictureFileName Char(50) '@PictureFileName'
)
insert into table3
SELECT * FROM OPENXML (@idoc, '/Database/Vehicle/Drivers/Driver',1)
with(
ExamineNumber Char(30) '/Database/Vehicle/@ExamineNumber',
DriverName Char(10),
IDCard Char(20),
Tel Char(50) '@TEL',
Memo Char(200) '@MEMO'
)
EXEC sp_xml_removedocument @idoc
fetch next from tb into @fn
end
close tb
deallocate tb
go