有三个表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语句?
现在要通过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语句?
SELECT DISTINCT A.ID, A.Field1,A.Field2,B.Field1,C.Field1
FROM A INNER JOIN B ON A.ID = B.ID INNER JOIN C ON A.ID=C.ID
)report
for xml auto, elements
<reports>
<report><a>
<id>1</id>
<a字段1></a字段1>
<a字段2></a字段2>
</a
<b>
<id>1</id>
<b字段1></b字段1>
</b>
<c>
<id>1</id>
<c字段1></c字段1>
</c>
</report>
<report>
...
...
...
declare @str varchar(8000)
set @str='<reports>'@str='<report><id>'+str(ID)+'</id><'+a字段1+'></'+a字段1+'><'+a字段2+'></'+a字段2+'><'+b字段1+'></'+b字段1+'><'+c字段1+'></'+c字段1+'></report>'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
????
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