代码改变世界

xml 数据 导入sql05

2011-08-06 23:13  曾祥展  阅读(689)  评论(0编辑  收藏

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'
       )