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

利用OpenXML导入网易Lofter的XML数据到SQL Server

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