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

本文转自曾祥展博客园博客，原文链接：http://www.cnblogs.com/zengxiangzhan/archive/2011/08/06/2129727.html，如需转载请自行联系原作者