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

Question

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?

<MyRootElement>
    <MyGroupingTag Group="1">
      <MyTag Value="1"      />
      <MyTag Value="Bert"   />    
      <MyTag Value="25"     />
      <MyTag Value="blonde" />
      <MyTag Value="1"      />
    </MyGroupingTag>
    <MyGroupingTag Group="2">
      <MyTag Value="2"        />
      <MyTag Value="Agnes"    />    
      <MyTag Value="61"       />
      <MyTag Value="white"    />
      <MyTag Value="0"        />
    </MyGroupingTag>
    <MyGroupingTag Group="3">
      <MyTag Value="3"         />
      <MyTag Value="Diane"     />    
      <MyTag Value="43"        />
      <MyTag Value="brunette"  />
      <MyTag Value="1"         />
    </MyGroupingTag>
</MyRootElement>

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.

Answer 1

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:

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

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