sql生成XML固定格式问题

[panshubing11]

表结构为：
表名：ab
a b
a1 b1
a2 b2
现有sql语句：
SELECT a AS 'Cols' ,b AS 'Cols' FROM dbo.ab FOR XML PATH('Rows')
执行完格式为：<Rows><Cols>a1b1</Cols></Rows><Rows><Cols>a2b2</Cols></Rows>
但是我不想要两个字段合并格式，理想格式为：<Rows><Cols>a1</Cols><Cols>b1</Cols></Rows><Rows><Cols>a2</Cols><Cols>b2</Cols></Rows>

[zbdzjx]

刚试了一下，如果“a AS 'Cols' ,b AS 'Cols'”里面的别名一样，就会混在一起，除非不一样，例如“a AS 'Cols1' ,b AS 'Cols2'”

[吉普赛的歌]

DECLARE @t TABLE (a VARCHAR(10),b VARCHAR(10))
INSERT INTO @t(a,b) VALUES('a1','b1')
INSERT INTO @t(a,b) VALUES('a2','b2')

SELECT CAST(REPLACE(REPLACE(
		(SELECT a AS 'Cols1',b AS 'Cols2' FROM @t FOR XML PATH('Rows'))
		,'Cols1','Cols')
		,'Cols2','Cols') AS XML)
/*
<Rows><Cols>a1</Cols><Cols>b1</Cols></Rows><Rows><Cols>a2</Cols><Cols>b2</Cols></Rows>
*/