That is a bit of odd looking XML you want to produce. You are relying on element position to distinguish what value is what. declare @T table ( ID int, Name varchar(10), Age int, HairColor varchar(10), HasCar bit ); insert into @T(ID, Name, Age, HairColor, HasCar) values (1, 'Bert' , 25, 'blonde' , 1), (2, 'Agnes', 61, 'white' , 0), (3, 'Diane', 43, 'brunette', 1); select row_number() over(order by T.ID) as '@Group', T.ID as 'MyTag/@Value', null, T.Name as 'MyTag/@Value', null, T.Age as 'MyTag/@Value', null, T.HairColor as 'MyTag/@Value', null, T.HasCar as 'MyTag/@Value' from @T as T for xml path('MyGroupingTag'), root('MyRootElement');