Manipulating tag names and output structure in SQL

Question

I have a SQL query that produces one row, with multiple columns from Microsoft SQL Server 2016.

I am trying to produce output in XML format, and can use the SQL XML basic parameters to get variations of:

<FirstName>Fred</FirstName>
<LastName>Smith</LastName>
..
..
..

What I need is:

<Answer name=“FirstName”>
  <value>Fred</value>
</Answer>
<Answer name=“LastName”>
  <value>Smith</value>
</Answer>
..
..
..

(I realize this may be an inefficient structure for XML - I didn't design the target!)

Here is the example:

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

SELECT FirstName, LastName FROM @tbl FOR XML  PATH('')

Produces:

<FirstName>Fred</FirstName>
<LastName>Smith</LastName>
<FirstName>Anna</FirstName>
<LastName>Polack</LastName>

If I use the following:

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

then I can get what I need:

<Answer name="FirstName"><value>Fred</value></Answer>
<Answer name="LastName"><value>Smith</value></Answer>
<Answer name="FirstName"><value>Anna</value></Answer> 
<Answer name="LastName"><value>Polack</value></Answer>

Is there is a more straightforward approach that doesn't require me to explicitly enumerate all the columns?

Thanks in advance.

Yitzhak Kabinsky's SQL #2 does this well.

Answer 1

A minimal reproducible example is not provided. Shooting from the hip.

Notable points:

• CROSS APPLY is producing a generic XML regardless of the table structure.
• XQuery FLWOR expression is composing a precise desired XML output.

Overall, the answer is following the same minimal reproducible example pattern. You copy it to SSMS as-is, and it is working.

SQL

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

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

Output

+----+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID |                                                                         Result                                                                         |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | <Answer name="ID"><value>1</value></Answer><Answer name="FirstName"><value>Fred</value></Answer><Answer name="LastName"><value>Smith</value></Answer>  |
|  2 | <Answer name="ID"><value>2</value></Answer><Answer name="FirstName"><value>Anna</value></Answer><Answer name="LastName"><value>Polack</value></Answer> |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------+

SQL #2

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

Output

<Answer name="ID">
  <value>1</value>
</Answer>
<Answer name="FirstName">
  <value>Fred</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>