[原创]利用OpenXML导入网易Lofter的XML数据到SQL Server

利用OpenXML导入网易Lofter的XML数据到SQL Server
[sql]
declare @xmlText xml
declare @club varchar(50)
DECLARE @idoc int

set @xmlText = (select doc from openrowset(bulk 'E:\Projects\doututu\data\lofter.xml',single_blob) as t(doc))

EXEC sp_xml_preparedocument @idoc OUTPUT,@xmlText

set @club='golf'

SELECT * into lofterBlogExport
FROM OPENXML (@idoc, '/lofterBlogExport/PostItem',2)
WITH(
title ntext,
publishTime nvarchar(50),
modifyTime nvarchar(50),
type nchar(10),
permalink nvarchar(50),
tag nvarchar(max),
ip nvarchar(50),
caption nvarchar(MAX),
photoLinks nvarchar(max),
photoCaptions nvarchar(max),
photoExifs nvarchar(max)
)

EXEC sp_xml_removedocument @idoc
[/sql]