将xml导入数据库

xml 字符串

DECLARE @idoc int
DECLARE @doc xml
SELECT @doc= N'<shopcats_list_get_response><shop_cats list="true"><shop_cat><cid>13</cid><is_parent>false</is_parent><name>手机</name><parent_cid>0</parent_cid></shop_cat><shop_cat><cid>1154</cid><is_parent>false</is_parent><name>服饰配件/皮带/帽子/围巾</name><parent_cid>0</parent_cid></shop_cat></shop_cats></shopcats_list_get_response>'
EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc
SELECT * into #temp FROM OPENXML (@Idoc, '/shopcats_list_get_response/shop_cats/shop_cat',2)
WITH ([cid] bigint,[is_parent] bit,[name] nvarchar(10),[parent_cid] bigint)
select * from #temp   

 

xml 文件  ，在将定义@doc的格式该成从文件加载(未测试)

SELECT @doc=bulkcolumn FROM OPENROWSET(
　　BULK ’D: test.xml’,
　　SINGLE_BLOB) AS x