Using FOR XML PATH based on column values

Question

Based on the following DDL:

CREATE TABLE #Records
(
    ID INT IDENTITY PRIMARY KEY,
    ResourceKey VARCHAR(500),
    [Value] VARCHAR(500)
)

INSERT INTO #Records ([ResourceKey], [Value])
VALUES 
('Root/Person/Age', '30'),
('Root/Person/Gender', 'Male'),
('Root/Person/Education/ElementarySchool', 'Sunnyside'),
('Root/Person/Education/HighSchool', 'Green Acres')

Is there any way, using FOR XML (or any other approach), to generate the following output?

<Root>
    <Person>
        <Age>30</Age>
        <Gender>Male</Gender>
        <Education>
            <ElementarySchool>Sunnyside</ElementarySchool>
            <HighSchool>Green Acres</HighSchool>
        </Education>
    </Person>
</Root>

I know that the following query would work for a specific node, but clearly wouldn't work for multiple nodes:

SELECT
    [Value] AS "Root/Person/Age"
FROM #Records
WHERE ID = 1
FOR XML PATH('')

NOTE - I am okay with a solution that involves iterating through each row one at a time, and inserting them into an XML doc. I briefly considered the insert XML DDL statement, however, I'm unfamiliar with how XML docs in SQL Server work, so I don't know exactly how I would formulate it, syntactically.

EDIT

To clarify, ResourceKey values are guaranteed to be unique. The example data is just to simplify constructing the query. Practically speaking, the table holds localization strings, which we need to convert to the listed XML format.

Answer 1

I guess you can do something like this:

CREATE TABLE #Records
(
    ID INT IDENTITY PRIMARY KEY,
    ResourceKey VARCHAR(500),
    [Value] VARCHAR(500)
)

INSERT INTO #Records ([ResourceKey], [Value])
VALUES 
('Root/Person/Age', '30'),
('Root/Person/Gender', 'Male'),
('Root/Person/Education/ElementarySchool', 'Sunnyside'),
('Root/Person/Education/HighSchool', 'Green Acres')

DECLARE @SQL NVARCHAR(MAX)

SELECT  @SQL = stuff(
(
        SELECT  ',max(case when resourcekey = ' + QUOTENAME(resourcekey, '''') + ' then Value end) AS ' + QUOTENAME(resourcekey, '''')
        FROM    #Records
        FOR xml path(''), type).value('.', 'nvarchar(max)')
        , 1, 1, '')

SET @SQL = 'select ' + @SQL + ' from #records for xml path(''''),type'
EXEC    (@SQL)

This builds your query by gathering all the resource keys and doing a little conditional aggregation to get the field values.

Just for kicks i used FOR XML to concatenate strings instead of more modern STRING_AGG.

If your resourcekey is a very long string, you can replace QUOTENAMEs with '''' + REPLACE(resourcekey, '''', '''''') + ''''