sql xml文件 导入mysql_纯SQL在SQLSERVER下执行Xml的导入和导出

Xml文件：

SQL:

DECLARE @idoc int  --定义一个指向Xml节点路径的指针

DECLARE @xml xml

SELECT @xml=bulkcolumn FROM OPENROWSET( BULK 'D:\ReadWriteXML\FTPInfos.xml', SINGLE_BLOB) AS x --打开一个Xml

SELECT @xml

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

SELECT *

FROM OPENXML (@idoc, '/FTPInfos/FTPInfo',0) --参数定义看上边的参考链接

WITH (UserName nvarchar(30) ,

Password nvarchar(20),

Id nvarchar(20),

FileNameStart nvarchar(20) '/FTPInfos/FTPInfo/Download/@FileNameStart',

FileExtension nvarchar(20) '/FTPInfos/FTPInfo/Download/@FileExtension',

FTPPath nvarchar(20) '/FTPInfos/FTPInfo/Download/@FTPPath',

LocalTempDir nvarchar(20) '/FTPInfos/FTPInfo/Download/@LocalTempDir',

LocalTargetDir nvarchar(20) '/FTPInfos/FTPInfo/Download/@LocalTargetDir',

LocalBackupDir nvarchar(20) '/FTPInfos/FTPInfo/Download/@LocalBackupDir',

FileType nvarchar(20) '/FTPInfos/FTPInfo/Download/ServiceDisplay/@FileType',

Remark nvarchar(20) '/FTPInfos/FTPInfo/Download/ServiceDisplay/@Remark',

Enabled nvarchar(20) '/FTPInfos/FTPInfo/Download/ServiceControl/@Enabled',

CronExpression nvarchar(20) '/FTPInfos/FTPInfo/Download/ServiceControl/@CronExpression',

ThreadCount nvarchar(20) '/FTPInfos/FTPInfo/Download/ServiceControl/@ThreadCount'

)

SELECT *

FROM OPENXML (@idoc, '/FTPInfos/FTPInfo',0)

WITH (UserName nvarchar(30) ,

Password nvarchar(20),

Id nvarchar(20),

FileNameStart nvarchar(20) '/FTPInfos/FTPInfo/Upload/@FileNameStart',

FileExtension nvarchar(20) '/FTPInfos/FTPInfo/Upload/@FileExtension',

FTPTargetPath nvarchar(20) '/FTPInfos/FTPInfo/Upload/@FTPTargetPath',

FTPTempPath nvarchar(20) '/FTPInfos/FTPInfo/Upload/@FTPTempPath',

LocalSourceDir nvarchar(20) '/FTPInfos/FTPInfo/Upload/@LocalSourceDir',

LocalBackupDir nvarchar(20) '/FTPInfos/FTPInfo/Upload/@LocalBackupDir',

FileType nvarchar(20) '/FTPInfos/FTPInfo/Upload/ServiceDisplay/@FileType',

Remark nvarchar(20) '/FTPInfos/FTPInfo/Upload/ServiceDisplay/@Remark',

Enabled nvarchar(20) '/FTPInfos/FTPInfo/Upload/ServiceControl/@Enabled',

CronExpression nvarchar(20) '/FTPInfos/FTPInfo/Upload/ServiceControl/@CronExpression',

ThreadCount nvarchar(20) '/FTPInfos/FTPInfo/Upload/ServiceControl/@ThreadCount'

)

EXEC sp_xml_removedocument @idoc --释放指针

以上就是读取附件Xml的SQL,读取到的Download和Upload会保存到两个表，这里就不做insert了。

备注：因为数据库是服务器的，所以读取的xml的绝对路径也是服务器的,字段定义根据需求来。

做法是使用XP_CMDSHELL+BCP写出的SQL执行的：

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

EXEC sp_configure 'xp_cmdshell', 1

GO

RECONFIGURE

GO

EXEC XP_CMDSHELL 'BCP "SELECT(SELECT Id as ''student/@studentID'',Code ''student/@studentName'',Name ''student/teacher/@teacherID'',ParameterValue ''student/teacher/@teacherName'',Description ''student/teacher/Address/@teacherAddress'', Id as ''student1/@studentID'',Code ''student1/@studentName'',Name ''student1/teacher/@teacherID'',ParameterValue ''student1/teacher/@teacherName'',Description ''student1/teacher/Address/@teacherAddress'' FROM [Xxx.Module].[dbo].[SysParameter] FOR XML path(''FTPInfo''),TYPE, ELEMENTS ,ROOT(''FTPInfos''))AS XML_Order " QUERYOUT "D:\text.xml" -c -T -x' --注：这段SQL，如果用的是查询语句，必须放在一行，换行会报错，不知道为什么，如果查询SQL太长可以写个存储过程，然后放进来,类似于下边注释的这种

--DECLARE @Sqlcmd NVARCHAR(max)

--SET @Sqlcmd='EXEC master.sys.xp_cmdshell ''bcp "EXEC Demo.dbo.p1" queryout E:\hqdst\'+ CONVERT(VARCHAR(10),GETDATE(),112)+REPLACE(CONVERT(VARCHAR(8),GETDATE(),8),':','')+'.xml -c -q -T'''

--EXEC(@Sqlcmd)

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

EXEC sp_configure 'xp_cmdshell', 0

GO

RECONFIGURE

GO

结果：