纯SQL在SQLSERVER下执行Xml的导入和导出

1、读取Xml文件，方法是使用OPENXML，具体参考：https://www.cnblogs.com/JuneZhang/p/5146392.html

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的绝对路径也是服务器的,字段定义根据需求来。

2、输出Xml到服务器物理路径下，这里随便找了个表生成了一个Xml.参考：https://www.cnblogs.com/biwork/p/3431257.html

 

做法是使用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

 结果：