这样的需要怎么弄成xml啊？

[freedomfans]

表 mytable:
location value
A1 1
A2 3
B1 2
B2 5
C1 3
C2 5
......
怎么才能得到这样的结果:
<A><A1>1</A1><A2>3</A2</A>
<B><B1>2</B1><B2>5</B2></B>
<C><C1>3</C1><C2>5</C2></C>
......

谢谢

[yangys]

create table mytable
(
location varchar(100),
value int
)

insert mytable
select 'A1',1
union all
select 'A2',3
union all
select 'b1',2
union all
select 'b2',5
union all
select 'c1',3
union all
select 'c2',5



create function fGetXml(@Item varchar(100))
returns varchar(8000)
as
begin
declare @str varchar(8000)
select @str=''

select
@str=@str+'<'+location +'>' +cast([value] as varchar(50)) +'</'+location +'>'
from mytable
where left(location,1)=@Item
return @str
end

select *,left(location,1) grp into #tmp from mytable

select '<'+grp+'>'+dbo.fGetXml(grp) +'<'+grp+'/>'
from #tmp
group by grp

---测试结果
<A><A1>1</A1><A2>3</A2><A/>
<b><b1>2</b1><b2>5</b2><b/>
<c><c1>3</c1><c2>5</c2><c/>
--

[yangys]

写错了斜线位置

[freedomfans]

怎么没人回答啊？分少可以加的哦

[badyue]

那把XML转成SQL怎么做?