XML Set the Element Name from Attribute Value

Question

I have a table of answers from a submission form that I need to convert to an XML file and upload to a website. Each row is one answer from one submissions and the answers need to be stored as the element name.

Table example: Note: Answer is a string value. The form has multiple questions, I'm only concerned with the Answers.

Submission ID	Answer
1	foo
1	bar
2	baz

Then the XML should like this:

<allSubmissions>
    <submission>
        <foo />
        <bar />
    </submission>
    <submission>
        <baz />
    </submission>
</allSubmissions>

How can I accomplish this? I've been successful in using the FOR XML EXPLICIT to format tables where the column name is equal to the XML tag name, but not where the value from the column is supposed to be the name of the tag.

I would prefer NOT use some sort of concatenation work around if possible since I'd like to save the results to an XML file.

Answering the questions in the comment.

XML is the required file type I need, so no CSV, text file, etc.
Correction; I mispoke when I said "any string value". The answer will always be a string and it comes from a pre-defined list of about ~10 values. So I shouldn't have to worry about an illegal element name like something that starts with a number.
The example format provided is the required schema that the validator tests against. So unfortunately, I can't do something like <answer>foo</answer>
A text file that looks like XML would work provided that I can convert to an XML file.

answers need to be stored as the element name and Answer could be any string value. Might I suggest to you then that XML is the wrong choice? Among other things XML element names have naming rules, so you can't have one named foo bar (with a space) or 1baz (starting with a digit), nor can they start with the letters XML Xml xml. — AlwaysLearning
– AlwaysLearning, Commented Sep 27 at 4:15
Why not <submission><id>1</id><answer>foo</answer><answer>bar</answer></submission>? — MatBailie
– MatBailie, Commented Sep 27 at 8:53
Do you want an XML file that will actually pass an XML validator, or do you want a text file that contains probably-not-valid-but-looks-like-XML? — Aaron Bertrand
– Aaron Bertrand, Commented Sep 27 at 13:20

Charlieface · Accepted Answer · 2025-09-27 23:18:55Z

Since you know all 10 values upfront, you can just output each column using CASE

But you want a grouped output by SubmissionID, so you are going to have to use FOR XML EXPLICIT, which can be very fiddly.

Each grouped row becomes a single <submission> node, so Tag can be 1 and Parent can be NULL.

Then each of those values becomes a separate column, where you do a pivot, outputting either '' to get an empty node, or NULL to omit the node. The node name goes into the column name as shown.

SELECT
  1 AS Tag,
  NULL AS Parent,
  --t.SubmissionID AS [submission!1!SubmissionID!hide],
  MIN(CASE WHEN t.Answer = 'foo' THEN '' END) AS [submission!1!foo!element],
  MIN(CASE WHEN t.Answer = 'bar' THEN '' END) AS [submission!1!bar!element],
  MIN(CASE WHEN t.Answer = 'baz' THEN '' END) AS [submission!1!baz!element]
FROM YourTable t
GROUP BY
  t.SubmissionID
ORDER BY
  t.SubmissionID
FOR XML EXPLICIT, ROOT('allSubmissions'), TYPE;

db<>fiddle

Charlieface · Accepted Answer · 2025-09-28 12:21:42Z

0

I will suggest a mix of other answers - it will make use of FOR XML directive and also use dynamic SQL to not work with statically listed options from the column.

Here's the query:

DECLARE @cols NVARCHAR(MAX) = '';

-- Build dynamic columns based on distinct answers
SELECT @cols = STRING_AGG(
  'MAX(CASE WHEN Answer = ' + QUOTENAME(Answer, '''') + ' THEN '''' END) AS ' + QUOTENAME(Answer),
  ', ')
FROM (SELECT DISTINCT Answer FROM Answers) d;

-- Build the dynamic query
DECLARE @sql NVARCHAR(MAX) = '
SELECT ' + @cols + '
FROM Answers
GROUP BY SubmissionID
FOR XML PATH(''submission''), ROOT(''allSubmissions'')';

-- Execute it
EXEC(@sql);

DB fiddle

edited Sep 28 at 12:21

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

answered Sep 28 at 7:03

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

1 Comment

Charlieface Sep 28 at 12:23

Also aggregation via a variable is not recommended and may give incorrect results, see learn.microsoft.com/en-us/sql/t-sql/language-elements/… Use STRING_AGG instead

Yitzhak Khabinsky · Accepted Answer · 2025-09-30 17:50:57Z

0

Initially, my first idea was to try to use XQuery with FLWOR expression. Unfortunately, SQL Server's XQuery FLWOR expression does not support dynamic construction of XML element names. This is a key limitation: SQL Server’s XQuery is based on a restricted subset of XQuery 1.0, and dynamic element construction (e.g., <{Answer}/> where Answer is a variable or column) is not allowed.

But we can use FOR XML PATH creatively with dynamic SQL and string concatenation to simulate composition of the XML fragments.

This method is NOT requiring to know upfront a predefined list of answers.

dbfiddle

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (SubmissionID INT, Answer VARCHAR(100));
INSERT @tbl (SubmissionID, Answer) VALUES
('1', 'foo'),
('1', 'bar'),
('2', 'baz');
-- DDL and sample data population, end

DECLARE @result NVARCHAR(MAX) = '<allSubmissions>';

SELECT @result = @result + '<submission>' +
  (
    SELECT '<' + Answer + ' />'
    FROM @tbl AS t2
    WHERE t2.SubmissionID = t1.SubmissionID
    FOR XML PATH(''), TYPE
  ).value('.', 'NVARCHAR(MAX)')
  + '</submission>'
FROM (SELECT SubmissionID FROM @tbl GROUP BY SubmissionID) AS t1;

SET @result = @result + '</allSubmissions>';

SELECT TRY_CAST(@result AS XML) AS FinalXml;

Output

<allSubmissions>
  <submission>
    <foo />
    <bar />
  </submission>
  <submission>
    <baz />
  </submission>
</allSubmissions>

edited Sep 30 at 17:50

answered Sep 28 at 1:19

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

5 Comments

Charlieface Sep 28 at 10:28

Any particular reason to avoid STRING_AGG?

Yitzhak Khabinsky Sep 28 at 12:31

Oops. I missed that the OP is on the SQL Server 2022. But that's okay. The answer will work on a wide range of SQL Server versions.

sushi Sep 30 at 16:43

Swapping out the table names, this yields FinalXml as the column name and NULL as the value. Not sure if it makes a difference, but I'm using a common table in place of @tbl (your declared temp table)

Yitzhak Khabinsky Sep 30 at 17:36

My answer follows a so called minimal reproducible example pattern. You copy it to SSMS as-is, run it , and it works as expected. Probably there is a difference in data sample that you provided. Table could be any real persisted db table, it makes no difference in comparison with table variable @tbl.

Yitzhak Khabinsky Sep 30 at 17:41

I added a dbfiddle to the answer.

Collectives™ on Stack Overflow

XML Set the Element Name from Attribute Value

3 Answers 3

Comments

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related