22,206
社区成员
发帖
与我相关
我的任务
分享
--导出
EXEC master.dbo.xp_cmdshell 'bcp "SELECT * FROM SampleDB.dbo.Tab1 FOR XML PATH(''MeasureSyParas''),ROOT(''MeasureSyParas'')" queryout D:\Tab.xml -c -T -S localhost'
--导入
INSERT INTO dbo.Tab1
( ID, Name )
SELECT
ID=T2.C.value('(ID/text())[1]','int'),
NAME=T2.C.value('(Name/text())[1]','nvarchar(50)')
FROM (SELECT CONVERT(xml, BulkColumn, 2) FROM OPENROWSET(Bulk 'D:\Tab.xml', SINGLE_BLOB) AS T) AS T(C)
CROSS APPLY T.C.nodes('MeasureSyParas/MeasureSyParas') AS T2(C)
--创建格式
EXEC master.dbo.xp_cmdshell 'bcp SampleDB.dbo.Tab1 format nul -c -x -f "D:\Tab-c.xml" -t, -T'
--导出时指定格式
EXEC master.dbo.xp_cmdshell 'bcp SampleDB.dbo.Tab1 out D:\Tab.xml -N -f "D:\Tab-c.xml" -T -S localhost'
--导入时指定格式
EXEC master.dbo.xp_cmdshell 'bcp SampleDB.dbo.Tab1 in D:\Tab.xml -N -f "D:\Tab-c.xml" -T -S localhost'
SELECT * FROM dbo.Tab1
/*
ID Name
1 Roy
1 Roy --新导入
*/