create table tb(DepartID INT,DepartName VARCHAR(20),ParentID INT) insert tb select 1,'dname',2 union all select 2,'ename',3 union all select 3,'fname',3 union all select 4,'gname',3 union all select 5,'hname',3 go
--输出为xml文件,文件名为: c:\a.xml exec master..xp_cmdshell 'bcp "select ''<root>'' union all select '' <Deprat DepartID=''+char(34)+''''+cast(DepartID as varchar)+''''+char(34)+'' DepartName=''+char(34)+''''+rtrim(DepartName)+''''+char(34)+'' ParentID=''+char(34)+''''+cast(ParentID as varchar)+''''+char(34)+'' />'' from tempdb.dbo.tb union all select ''</root>''" queryout "c:\a.xml" /U"sa" /P"" /c' go
--示例数据
use tempdb
go
create table tb(DepartID INT,DepartName VARCHAR(20),ParentID INT)
insert tb select 1,'dname',2
union all select 2,'ename',3
union all select 3,'fname',3
union all select 4,'gname',3
union all select 5,'hname',3
go
--输出为xml文件,文件名为: c:\a.xml
exec master..xp_cmdshell 'bcp "select ''<root>'' union all select '' <Deprat DepartID=''+char(34)+''''+cast(DepartID as varchar)+''''+char(34)+'' DepartName=''+char(34)+''''+rtrim(DepartName)+''''+char(34)+'' ParentID=''+char(34)+''''+cast(ParentID as varchar)+''''+char(34)+'' />'' from tempdb.dbo.tb union all select ''</root>''" queryout "c:\a.xml" /U"sa" /P"" /c'
go
--删除测试
drop table tb
/*--最后生成的c:\a.xml文件内容
<root>
<Deprat DepartID="1" DepartName="dname" ParentID="2" />
<Deprat DepartID="2" DepartName="ename" ParentID="3" />
<Deprat DepartID="3" DepartName="fname" ParentID="3" />
<Deprat DepartID="4" DepartName="gname" ParentID="3" />
<Deprat DepartID="5" DepartName="hname" ParentID="3" />
</root>
--*/