日期:2014-05-18  浏览次数:20439 次

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> 
--*/