/*

EXEC master..xp_cmdshell ‘BCP "select * from NBZA_Live_20140613.dbo.StoreList for xml Raw " queryout D:\XML\my_StoreList_20140630001.xml -c -U"sa" -P"raym" -S"(local)" ‘

CREATE TABLE StoreList_xml_Table (XmlCol xml);

INSERT StoreList_xml_Table(XmlCol)
SELECT CONVERT(xml, BulkColumn, 2) FROM
OPENROWSET(Bulk ‘D:\XML\my_StoreList_20140630001.xml‘, SINGLE_BLOB) [rowsetresults];

Drop table StoreList_xml_Table
select * From StoreList_xml_Table

*/



DECLARE @XMLdoc XML --这里看成一个从存储过程传进来的参数
DECLARE @idoc int
select @XMLdoc = XMLCOL From StoreList_xml_Table
EXEC sp_xml_preparedocument @idoc OUTPUT,@XMLdoc --创建XML 句柄

-- 查询结果
SELECT * into #storeList FROM OPENXML (@idoc,‘/root/row‘,1)
with
( Store_code Nvarchar(100),
Name Nvarchar(100),
Store_type Nvarchar(100),
address Nvarchar(100),
currency_id Nvarchar(100)
)

EXEC sp_xml_removedocument @idoc --清理一下内存



select * From #storeList

Drop table #storeList