Creating dynamic XML tag names for output from SQL

(This is a follow on to https://stackoverflow.com/questions/75861339/manipulating-tag-names-and-output-structure-in-sql which was well answered by Yitzhak Khabinsky)

I am trying to extract data from Microsoft SQL Server 2016 in specific XML formats. I can get close with standard FOR XML statements, but do not have any experience using XQuery - which I suspect is at the heart of an elegant solution.

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, FirstName 
VARCHAR(20), MiddleName VARCHAR(20), LastName VARCHAR(20));
INSERT @tbl (FirstName, MiddleName, LastName) VALUES
('Fred', 'A.','Smith'),
('Anna', NULL,'Polack');
-- DDL and sample data population, end

select * from @tbl

;WITH rs(x) AS
(
    SELECT * 
    FROM @tbl
    FOR XML PATH(''), TYPE, ROOT('root')
)
SELECT
 x.query('for $x in /root/*
        return <Answer name="{local-name($x)}">
          <value>{data($x)}</value>
        </Answer>') AS Result 
FROM rs

Produces the following:

<Answer name="ID">
  <value>1</value>
</Answer>
<Answer name="FirstName">
  <value>Fred</value>
</Answer>
<Answer name="MiddleName">
  <value>A.</value>
</Answer>
<Answer name="LastName">
  <value>Smith</value>
</Answer>
<Answer name="ID">
  <value>2</value>
</Answer>
<Answer name="FirstName">
  <value>Anna</value>
</Answer>
<Answer name="LastName">
  <value>Polack</value>
</Answer>

I would like to get more explicit enumeration of the output like the following:

<Answer name="ID_1">
  <value>1</value>
</Answer>
<Answer name="FirstName_1">
  <value>Fred</value>
</Answer>
<Answer name="MiddleName_1">
  <value>A.</value>
</Answer>
<Answer name="LastName_1">
  <value>Smith</value>
</Answer>
<Answer name="ID_2">
  <value>2</value>
</Answer>
<Answer name="FirstName_2">
  <value>Anna</value>
</Answer>
<Answer name="LastName_2">
  <value>Polack</value>
</Answer>

I can hack it by using:

select STRING_AGG('<Answer name="'+ColKey+'">'+ 
'<value>'+ColValue+'</value></Answer>',' ')
from
(
select  tab.* from @tbl
CROSS APPLY (
VALUES  ('FirstName_'+cast(ID as varchar), FirstName),
('LastName_'+cast(ID as varchar), LastName)
) tab(ColKey, ColValue)
)t

But I'm sure there is a more elegant way using XQuery

No need for string_agg()

Example

Select  [Answer/@name] = concat('ID','_',id)
       ,[Answer/value] = id
	   ,null
	   ,[Answer/@name] = concat('FirstName','_',id)
       ,[Answer/value] = FirstName
	   ,null
	   ,[Answer/@name] = concat('MiddleName','_',id)
       ,[Answer/value] = MiddleName
	   ,null
	   ,[Answer/@name] = concat('LastName','_',id)
       ,[Answer/value] = LastName
 From   @tbl
 For xml path('')

Results

<Answer name="ID_1">
  <value>1</value>
</Answer>
<Answer name="FirstName_1">
  <value>Fred</value>
</Answer>
<Answer name="MiddleName_1">
  <value>A.</value>
</Answer>
<Answer name="LastName_1">
  <value>Smith</value>
</Answer>
<Answer name="ID_2">
  <value>2</value>
</Answer>
<Answer name="FirstName_2">
  <value>Anna</value>
</Answer>
<Answer name="MiddleName_2" />
<Answer name="LastName_2">
  <value>Polack</value>
</Answer>

UPDATE: For Many columns. Since you have string_agg()... I can assume you've got OPENJSON.

Example

Select [Answer/@name] = concat([key],'_',A.ID)
      ,[Answer/value] = B.value
 From  @tbl A
  Cross Apply  (
                 Select [Key]
                       ,Value
                 From OpenJson(  (Select A.* For JSON Path,Without_Array_Wrapper )  ) 
              ) B
 For xml path('')

You can use concat in the XQuery to add the ID value onto it.

You just need to break out the first generated XML in two levels

WITH rs(x) AS
(
    SELECT *
    FROM @tbl
    FOR XML PATH('row'), TYPE
)
SELECT
 x.query('for $x in /row
          for $y in $x/*
        return <Answer name="{concat(local-name($y), "_", ($x/ID/text())[1])}">
          <value>{data($y)}</value>
        </Answer>') AS Result 
FROM rs;

db<>fiddle