for xml的问题
如何得到执行 select * from Table1 for xml raw 后的XML值
[解决办法]
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>
--*/