本来是用程序导出的xml文件到C盘下面,现在想请教各位能不能用sql server存储过程来实现?
------解决方案--------------------
唐大神正在努力,稍安勿躁
------解决方案--------------------
用while实现也许比较麻烦喔,担心游标性能问题的话,可以用静态游标(cursor static).
导出xml文件的方法如下.
-- 主表 yshm.mid字段为主键
create table yshm(mid int,a varchar(10),b varchar(10),c varchar(10))
-- 明细表 yshd.mid字段对应yshm.mid字段
create table yshd(mid int,d varchar(10),e varchar(10),f varchar(10))
-- 中间表
create table xmltable(xmldata varchar(max))
insert into yshm
select 1,'a1','b1','c1' union all
select 2,'a2','b2','c2' union all
select 3,'a3','b3','c3'
insert into yshd
select 1,'d1','e1','f1' union all -- 1
select 1,'d2','e2','f2' union all
select 2,'d3','e3','f3' union all -- 2
select 3,'d4','e4','f4' union all -- 3
select 3,'d5','e5','f5'
declare @xmldata varchar(max),@mid int,@a varchar(10),@b varchar(10),@c varchar(10),@x varchar(max),@cmd varchar(8000)
select @xmldata='<?xml version="1.0" encoding="UTF-8"?>
<Document License="license" SN="computer" Version="3.0" xmlns:xsi="http://www1.drugadmin.com">
<Event ID="StockIn">'
declare ap cursor static for select mid,a,b,c from yshm
open ap
fetch first from ap into @mid,@a,@b,@c
while(@@fetch_status<>-1)
begin
select @xmldata=@xmldata
+'<Bill mid="'+rtrim(@mid)+'" cola="'+@a+'" colb="'+@b+'" colc="'+@c+'" >'
+'<DataField>',
@x=''
select @x=@x+'<Data cold="'+d+'" e="'+e+'" f="'+f+'" />'
from yshd where mid=@mid
select @xmldata=@xmldata+@x+'</DataField></Bill>'
fetch next from ap into @mid,@a,@b,@c
end
close ap
deallocate ap
select @xmldata=@xmldata+'</Event></Document>'
truncate table xmltable
insert into xmltable(xmldata) values(@xmldata)
exec master..xp_cmdshell 'bcp "select cast(xmldata as xml) from DBAP.dbo.xmltable" queryout D:\yangsh.xml -S "." -U "sa" -P "001332@qq" -c -w '
D:\yangsh.xml 文件如下图
------解决方案--------------------
写个类似的:
declare @xml nvarchar(max)
;with tt
as
(
select FromCorpID ,
ActDate ,
Actor ,
CorpOrderID ,
BillID ,
Name,
UpperCorpOrderID,
Code
from
(
select FromCorpID='00000000000000043621',
ActDate='2014-01-16 15:32:44' ,
Actor=N'蔡琳' ,
CorpOrderID='2995975' ,
BillID='20130002995975' ,
Name='102' ,
UpperCorpOrderID='' ,
Code='81206620073443503824'
union all
select FromCorpID='00000000000000043621',
ActDate='2014-01-16 15:32:44' ,
Actor=N'蔡琳' ,
CorpOrderID='2995975' ,
BillID='20130002995975' ,
Name='102' ,
UpperCorpOrderID='' ,
Code='81206620073443470219'
union all
select FromCorpID='00000000000000043621',
ActDate='2014-01-16 15:32:44' ,
Actor=N'蔡琳' ,
CorpOrderID='2995975' ,