从SQL存储过程格式化输出到xml文件

项目需要直接在SQLServer数据库中使用SP进行数据查询并格式化输出到xml文件。

花了一点时间，研究了下bcp。
大致的步骤就是：
1.查询数据格式化，并将其插入临时表。
2.临时表输出到xml。

写下的SP脚本如下：

/*-------------------------------------------
过程名称：导出xml格式文件
---------------------------------------------*/
CREATE proc [dbo].[sp_output_xml]
as 
begin
declare @fname varchar(max)
declare @xml varchar(max)

--text字段和其他字段做字符串操作，必须转换为字符串类型
declare @id varchar(20)
declare @name varchar(40)
declare @weight varchar(10)
declare @lon varchar(10)
declare @lat varchar(10)
declare @manufacturedate_dt varchar(20)

declare @count int
declare @tabname varchar(100)
declare @objectid int

set @tabname = 'BusStop'
set @fname = 'd:\busdata.xml'

-- temp table
create TABLE temptab( res nvarchar(4000) )

set @count = 0
select @count=count(*) from BusStop
--print @count
set @objectid = 1

while @objectid<=@count
begin
select @id=ID,@name=Name,@weight=Weight,@lon=Lon,@lat=Lat,@manufacturedate_dt=Manufacturedate_dt 
from BusStop where nObjectid = @objectid
if @objectid = 1
begin
select @xml = '<add><doc>
<field name="id">'+@id+'</field> 
<field name="name">'+@name+'</field> 
   <field name="weight">'+@weight+'</field> 
<field name="busstop">'+@lat+','+@lon+'</field> 
<field name="manufacturedate_dt">'+@manufacturedate_dt+'</field>
</doc>
' 
end
else if @objectid = @count
begin
select @xml = '<doc>
<field name="id">'+@id+'</field> 
<field name="name">'+@name+'</field> 
   <field name="weight">'+@weight+'</field> 
<field name="busstop">'+@lat+','+@lon+'</field> 
<field name="manufacturedate_dt">'+@manufacturedate_dt+'</field>
</doc></add>
' 
end
else
begin
select @xml = '<doc>
<field name="id">'+@id+'</field> 
<field name="name">'+@name+'</field> 
<field name="weight">'+@weight+'</field> 
<field name="busstop">'+@lat+','+@lon+'</field> 
<field name="manufacturedate_dt">'+@manufacturedate_dt+'</field>
</doc>
' 
end
--print @xml
insert into temptab (res) values (@xml)
set @objectid = @objectid + 1
end

-- 权限问题时，执行如下
--sp_configure 'show advanced options',1 
--RECONFIGURE
--sp_configure 'xp_cmdshell', 1;
--RECONFIGURE

-- testdb.dbo.temptab
exec ('master..xp_cmdshell ''bcp " select * from testdb.dbo.temptab" queryout '+@fname+' -U "sa" -P "99999999" -T -c''') 

drop table temptab

end