如何把三个关联表的数据导出为xml格式？
有三个表a,b,c，关联方式是通过每个表里的字段id
现在要通过select.......for xml的方式，把a,b,c表导为一份xml报告reports
格式是同一id的a表记录，b表记录，c表记录组成一份report
如下
<reports>
<report>
<id> 1 </id>
<a字段1> </a字段1>
<a字段2> </a字段2>
<b字段1> </b字段1>
<c字段1> </c字段1>
</report>
<report>
<id> 2 <id>
...
...
...
求高手写出此sql语句？
[解决办法]
declare @str varchar(8000)
set @str= ' <reports> '
SELECT @str= ' <report> <id> '+str(ID)+ ' </id> < '+a字段1+ '> </ '+a字段1+ '> < '+a字段2+ '> </ '+a字段2+ '> < '+b字段1+ '> </ '+b字段1+ '> < '+c字段1+ '> </ '+c字段1+ '> </report> '
FROM (
SELECT DISTINCT A.ID id, A.Field1 a字段1,A.Field2 a字段2,B.Field1 b字段1,C.Field1 c字段1
FROM A INNER JOIN B ON A.ID = B.ID INNER JOIN C ON A.ID=C.ID
)report
select @str=@str+ ' </reports> '
print @str
?????
[解决办法]
终于成功了!花了我两个多小时,一边看帮助,一边调试,终于得出了楼主想要的效果,但具体还不是很明白,继续学习吧!
给楼主看一下代码:
--test data
create table testa (aid int,aa varchar(10))
create table testb (bid int,bb varchar(10))
create table testc (cid int,cc varchar(10))
go
insert into testa
select 1, 'aaaa1 '
union all select 2, 'aaaa2 '
insert into testb
select 1, 'bbbb1 '
union all select 2, 'bbbb2 '
insert into testc
select 1, 'cccc1 '
union all select 2, 'cccc2 '
go
--select
select 1 as Tag,
NULL as Parent,
null as [reports!1!id],
null as [report!2!id!ELEMENT],
null as [report!2!aa!ELEMENT],
null as [report!2!bb!ELEMENT],
null as [report!2!cc!ELEMENT]
union all
select 2 as tag,
1 as parent,
null as [reports!1!id],
aid,
aa,
bb,
cc
from testa join testb on aid=bid join testc on bid=cid
for xml EXPLICIT
