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?

&lt;MyRootElement&gt;
    &lt;MyGroupingTag Group="1"&gt;
      &lt;MyTag Value="1"      /&gt;
      &lt;MyTag Value="Bert"   /&gt;    
      &lt;MyTag Value="25"     /&gt;
      &lt;MyTag Value="blonde" /&gt;
      &lt;MyTag Value="1"      /&gt;
    &lt;/MyGroupingTag&gt;
    &lt;MyGroupingTag Group="2"&gt;
      &lt;MyTag Value="2"        /&gt;
      &lt;MyTag Value="Agnes"    /&gt;    
      &lt;MyTag Value="61"       /&gt;
      &lt;MyTag Value="white"    /&gt;
      &lt;MyTag Value="0"        /&gt;
    &lt;/MyGroupingTag&gt;
    &lt;MyGroupingTag Group="3"&gt;
      &lt;MyTag Value="3"         /&gt;
      &lt;MyTag Value="Diane"     /&gt;    
      &lt;MyTag Value="43"        /&gt;
      &lt;MyTag Value="brunette"  /&gt;
      &lt;MyTag Value="1"         /&gt;
    &lt;/MyGroupingTag&gt;
&lt;/MyRootElement&gt;


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:

&lt;MyRootElement&gt;
  &lt;MyGroupingTag Group="1"&gt;
    &lt;MyTag Name="ID" Value="1" /&gt;
    &lt;MyTag Name="Name" Value="Bert" /&gt;
    &lt;MyTag Name="Age" Value="25" /&gt;
    &lt;MyTag Name="HairColor" Value="blonde" /&gt;
    &lt;MyTag Name="HasCar" Value="1" /&gt;
  &lt;/MyGroupingTag&gt;
  &lt;MyGroupingTag Group="2"&gt;
    &lt;MyTag Name="ID" Value="2" /&gt;
    &lt;MyTag Name="Name" Value="Agnes" /&gt;
    &lt;MyTag Name="Age" Value="61" /&gt;
    &lt;MyTag Name="HairColor" Value="white" /&gt;
    &lt;MyTag Name="HasCar" Value="0" /&gt;
  &lt;/MyGroupingTag&gt;
  &lt;MyGroupingTag Group="3"&gt;
    &lt;MyTag Name="ID" Value="3" /&gt;
    &lt;MyTag Name="Name" Value="Diane" /&gt;
    &lt;MyTag Name="Age" Value="43" /&gt;
    &lt;MyTag Name="HairColor" Value="brunette" /&gt;
    &lt;MyTag Name="HasCar" Value="1" /&gt;
  &lt;/MyGroupingTag&gt;
&lt;/MyRootElement&gt;


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