XML 导入导出示例

 

/*Step1: 使用BCP命令生成XML文件*/

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)" '

/*Step2: 将生成的xml文件读入到数据库中*/

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]; 

select * From StoreList_xml_Table

Drop table 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