xml 导入SQL Server 2005
==============XML 内容===========
<ROOT>
<ITEM>
<A>{data}</A>
<B>{data2}</B>
<C>{data3}</C>
<ITEM>
<ROOT>
1.利用vs2005 编写Job导入
2.直接利用sql server 2005语句导入
SELECT * into #temp FROM OPENROWSET(
BULK '\\{FileServerIP}\test\test.xml' ,SINGLE_BLOB ) AS x
DECLARE @hdoc int
DECLARE @doc xml
select @doc=BulkColumn from #temp
EXEC sp_xml_preparedocument @hdoc OUTPUT,@doc
insert into Table_Name(A,B,C)
SELECT * FROM OPENXML (@hdoc, '/ROOT/TIEM',3)
with (A numeric(13,3), B numeric(13,3), C varchar(50))--这句一定要和DB中Table 的ColumnsName对应
exec sp_xml_removedocument @hdoc
3.利用SSIS（DTS)导入
<ROOT>
<ITEM>
<A>{data}</A>
<B>{data2}</B>
<C>{data3}</C>
<ITEM>
<ROOT>
1.利用vs2005 编写Job导入
2.直接利用sql server 2005语句导入
SELECT * into #temp FROM OPENROWSET(
BULK '\\{FileServerIP}\test\test.xml' ,SINGLE_BLOB ) AS x
DECLARE @hdoc int
DECLARE @doc xml
select @doc=BulkColumn from #temp
EXEC sp_xml_preparedocument @hdoc OUTPUT,@doc
insert into Table_Name(A,B,C)
SELECT * FROM OPENXML (@hdoc, '/ROOT/TIEM',3)
with (A numeric(13,3), B numeric(13,3), C varchar(50))--这句一定要和DB中Table 的ColumnsName对应
exec sp_xml_removedocument @hdoc
3.利用SSIS（DTS)导入
