XML 匯入匯出樣本

標籤：2014   for   type   res   table   har   

/*

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