Creating dynamic XML tag names for output from SQL

Question

(This is a follow on to 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

Answer 1

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