[向zjcxc提问]关于用触发器自动导出生成xml格式文件的难题！

nv531f 2008-07-11 09:25:47

现有如下查询存储过程，我希望将此存储过程改成能导出格式为下列1，2，3要求的xml文件；并保存为自动生成字母+日期的文件名
(对应1：jh2008-07-10.xml 2:xs2008-07-10.xml 3:kc2008-07-10.xml)。
查询存储过程
CREATE PROC sbp_savea
@str_rq varchar(10),@djbh varchar(11)
AS

select a.rq,b.dwmch,b.xvkz,b.kehulb,b.danwbh,b.city_id,b.city_name,b.boro_id,b.boro_name,b.dzhdh,d.spmch,d.tongym,d.shpchd,d.shpgg,d.pizhwh,d.jixing,c.pihao,d.dw,c.shl into #a
from cwk a(nolock) join mchk b(nolock) on a.dwbh=b.dwbh join ywmxk c(nolock) on a.djbh=c.djbh join spkfk d(nolock) on c.spid=d.spid
where a.djbh like 'JH[A,C]%' and substring(a.djbh,1,2)=@djbh and a.rq=@str_rq
order by a.djbh

select a.rq,b.dwmch,b.xvkz,b.kehulb,b.danwbh,b.city_id,b.city_name,b.boro_id,b.boro_name,b.dzhdh,d.spmch,d.tongym,d.shpchd,d.shpgg,d.pizhwh,d.jixing,c.pihao,d.dw,c.shl into #b
from cwk a(nolock) join mchk b(nolock) on a.dwbh=b.dwbh join ywmxk c(nolock) on a.djbh=c.djbh join spkfk d(nolock) on c.spid=d.spid
where a.djbh like 'XS[A,C]%' and substring(a.djbh,1,2)=@djbh and a.rq=@str_rq
order by a.djbh

select @str_rq as rq,a.spbh,a.spmch,a.tongym,a.shpchd,a.shpgg,a.pizhwh,a.jixing,c.pihao,a.dw,c.sxrq,sum(c.shl) shl into #c
from spkfk a(nolock) join sphwph c(nolock) on a.spid=c.spid
where a.beactive='是' and c.pihao <>'' and a.spid in (select distinct spid from splsk where rq>'2007-12-31')
group by a.spbh,a.spmch,a.tongym,a.shpchd,a.shpgg,a.pizhwh,a.jixing,c.pihao,a.dw,c.sxrq having sum(c.shl) <>0
order by a.spbh

select * from #a

select * from #b

select * from #c

drop table #a
drop table #b
drop table #c

GO

希望将上面的查询存储过程改为能自动分别生成如下1，2，3格式的xml文件并保存为自动生成字母+日期的文件名
(对应1：jh2008-07-10.xml 2:xs2008-07-10.xml 3:kc2008-07-10.xml)的存储过程。

1:
<?xml version="1.0" encoding="GBK" ?>
<dwmch>ABC </dwmch>
<kehulb>1 </kehulb>
<danwbh>A001 </danwbh>
<xvkz>420123115246 </xvkz>
<city_id>112 </city_id>
<city_name>北京市 </city_name>
<boro_id>112-1 </boro_id>
<boro_name>海淀区 </boro_name>
<dzhdh>027-8888888 </dzhdh>
<rq>2008-07-10 </rq>
<pizhwh>QS-12154 </pizhwh>
<spmch>果冻 </spmch>
<tongym>果冻 </tongym>
<shpchd>北京 </shpchd>
<drug_ename />
<shpgg>12 </shpgg>
<jixing>SX </jixing>
<pihao>080506 </pihao>
<dw>kg </dw>
<shl>10 </shl>

2:
<?xml version="1.0" encoding="GBK" ?>
<dwmch>ABC </dwmch>
<kehulb>1 </kehulb>
<danwbh>A001 </danwbh>
<xvkz>420123115246 </xvkz>
<city_id>112 </city_id>
<city_name>北京市 </city_name>
<boro_id>112-1 </boro_id>
<boro_name>海淀区 </boro_name>
<rq>2008-07-10 </rq>
<dzhdh>027-88888888 </dzhdh>
<pizhwh>QS-12154 </pizhwh>
<spmch>果冻 </spmch>
<tongym>果冻 </tongym>
<shpchd>北京 </shpchd>
<shpgg>12 </shpgg>
<jixing>SX </jixing>
<pihao>080506 </pihao>
<dw>kg </dw>
<shl>5 </shl>

3:
<?xml version="1.0" encoding="GBK" ?>

<spbh>A001 </spbh>
<dzhdh>027-88888888 </dzhdh>
<pizhwh>QS-12154 </pizhwh>
<spmch>果冻 </spmch>
<tongym>果冻 </tongym>
<shpchd>北京 </shpchd>
<shpgg>12 </shpgg>
<jixing>SX </jixing>
<pihao>080506 </pihao>
<dw>kg </dw>
<rq>2008-07-10 </rq>
<shl>5 </shl>
需要说明的是：此查询存储过程有很多记录，希望改的存储过程能自动添加对应的记录。

...全文