XML导入SQL

create table table1(
ExamineNumber Char(30),
ExamineComany Char(5),
ExamineDatetime Datetime,
CompanyCode Char(10),
CompanyName Char(50),
CompanyTel Char(35),
VehicleCode Char(10),
ChinaNumber Char(10),
HKNumber Char(10),
VoitureBrand Char(12),
Deadweight int,
Tonnage int,
Seating int,
Color Char(10),
VoitureType Char(10),
VoitureModel Char(10),
EngineNumber Char(30),
ChassisNumber Char(30),
OilBox int,
OilBoxCapacity int,
SideDoor int,
Rump Char(1),
BreakWind Char(1),
ForesideBumper Char(1),
TailBumper Char(1),
Memo Char(200),
Status Char(1)
)

create table table2(
ExamineNumber Char(30),
PictureFileName Char(50)
)

create table table3(
ExamineNumber Char(30),
DriverName Char(10),
IDCard Char(20),
Tel Char(50),
Memo Char(200)
)



create proc p_inport
@path nvarchar(1000)='d:, --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(8000),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(8000)
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

truncate table #tb
fetch next from tb into @fn
end
close tb
deallocate tb
go