xml 数据 导入sql05
xml结构:
<?xml version="1.0" encoding="GBk"?>
<News>
<Item Title=" " Url=" " Time="2017-8-13 22:12:00" Content=" " />
<Item Title=" " Url=" " Time="2017-8-13 22:12:00" Content=" " />
<Item Title=" " Url=" " Time="2017-8-13 22:12:00" Content=" " />
</News>
SQL插入:
/************************************************************
* Code formatted by 曾祥展
* Time: 2011-8-6 23:06:57
************************************************************/
declare @idoc int;
declare @doc xml;
select @doc = bulkcolumn
from openrowset(
bulk 'C:\Users\zengzhan\Desktop\news\news.xml',
single_blob
) as x
exec sp_xml_preparedocument @Idoc output,
@doc
insert into [News].[dbo].[News]
(
[Title],
[Content],
[Url],
[Time]
)
select *
from openxml(@Idoc, '/News/Item ', 2)
with (
Title varchar(max) '@Title',
content varchar(max) '@Content',
url varchar(max) '@Url',
time datetime '@Time'
)