Creating XML-like output from SQL organized by columns rather than rows

Question

I am looking for an efficient way to produce output that looks like XML, grouped by columns of original data from SQL Server 2016, targeting a specific output format.

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

I can do this :

DECLARE @Try XML

SET @Try = (SELECT 
                ('<Answer name = "FirstName">' + 
                 (SELECT FirstName AS 'value' 
                  FROM @tbl 
                  ORDER BY ID
                  FOR XML PATH(''), ROOT('repeat'), ELEMENTS XSINIL) +
                 '</Answer>') 
            +
            (SELECT
                 ('<Answer name = "MiddleName">' +
                  (SELECT MiddleName AS 'value'
                   FROM @tbl 
                   ORDER BY ID
                   FOR XML PATH(''), ROOT('repeat'), ELEMENTS XSINIL) + 
                  '</Answer>')
            +
            (SELECT
                 ('<Answer name = "LastName">' +
                  (SELECT LastName AS 'value'
                   FROM @tbl 
                   ORDER BY ID
                   FOR XML PATH(''), ROOT('repeat'), ELEMENTS XSINIL) + 
                  '</Answer>')
           )))

SELECT @Try

to get:

<Answer name="FirstName">
  <repeat xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <value>Fred</value>
    <value>Anna</value>
  </repeat>
</Answer>
<Answer name="MiddleName">
  <repeat xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <value>A.</value>
    <value xsi:nil="true" />
  </repeat>
</Answer>
<Answer name="LastName">
  <repeat xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <value>Smith</value>
    <value>Polack</value>
  </repeat>
</Answer>

I can hack by converting to text and replacing the xmlns stuff to get desired output, which is:

<Answer name="FirstName">
  <repeat>
    <value>Fred</value>
    <value>Anna</value>
  </repeat>
</Answer>
<Answer name="MiddleName">
  <repeat>
    <value>A.</value>
    <value xsi:nil="true" />
  </repeat>
</Answer>
<Answer name="LastName">
  <repeat>
    <value>Smith</value>
    <value>Polack</value>
  </repeat>
</Answer>

But there must be a more efficient way of generating this output without explicitly querying every column (my actual data set has many columns).

Answer 1

The following code will do what you want, if not very efficiently.

• It begins by constructing the whole table as XML.
• Then it parses that XML as follows:
• Take the first row's nodes and use them to create header Answer/repeat nodes.
• For each of those, requery the XML for all the values.
• The step /row/*[local-name() = local-name($header)] is likely to be particularly costly

SELECT
  x.xml.query('
  for $header in /row[1]/*
  return
    <Answer name="{local-name($header)}">
      <repeat>{
        for $val in /row/*[local-name() = local-name($header)]
        return <value>{data($val)}</value>
      }</repeat>
    </Answer>')
FROM (
    SELECT *
    FROM @tbl
    FOR XML PATH('row'), TYPE
) x(xml);

db<>fiddle

It's probably more efficient to use your existing code and query the data multiple times.