ms sql query multiple table colums as identical tag (sequel)

Extending this original question

I am using SQL Server Express 2017

Pretending this is my source table:

+-------------+---------------------------+
| ID | Name   | Age  | HairColor | HasCar |
+-------------+---------------------------+
| 1  | Bert   | 25   | blonde    | 1      |
| 2  | Agnes  | 61   | white     | 0      |
| 3  | Diane  | 43   | brunette  | 1      |
+-------------+---------------------------+

How can get a result looking like this?

Because of the Group attribute in MyGroupingTag I can't use something like

SELECT... FOR XML PATH('MyTag'), ROOT('MyGroupingTag')

The Group itself is a simple row count.

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');


This might generate a better version of your XML.

select row_number() over(order by T.ID) as '@Group',
'ID' as 'MyTag/@Name',
T.ID as 'MyTag/@Value',
null,
'Name' as 'MyTag/@Name',
T.Name as 'MyTag/@Value',
null,
'Age' as 'MyTag/@Name',
T.Age as 'MyTag/@Value',
null,
'HairColor' as 'MyTag/@Name',
T.HairColor as 'MyTag/@Value',
null,
'HasCar' as 'MyTag/@Name',
T.HasCar as 'MyTag/@Value'
from @T as T
for xml path('MyGroupingTag'), root('MyRootElement');


Result:


























The null between columns in the column list is there to separate MyTag into different elements.

StackExchange Database Administrators Q#234011, answer score: 5