[SQL] Create an XML table from a SQL table where the SQL column headings are the first row?

I am trying to select values from a SQL table as an XML table with a particular format.

Using the following table...

SELECT x.*
INTO #t
FROM (VALUES (1, 'John', 'Doe')) x(Id, FirstName, LastName)

SELECT *
FROM #t

DROP TABLE #t


I would need an XML output that looked something like this...

<Table>
<Row>
<Column>Id</Column>
<Column>FirstName</Column>
<Column>LastName</Column>
</Row>
<Row>
<Column>1</Column>
<Column>John</Column>
<Column>Doe</Column>
</Row>
</Table>


I was able to get an output matching this using UNION and XML PATH...

SELECT t1.*
INTO #t
FROM (VALUES (1, 'John', 'Doe')) t1(Id, FirstName, LastName);

WITH t2(xmlTable) AS (
SELECT t3.Id 'Column'
,''
,t3.FirstName 'Column'
,''
,t3.LastName 'Column'
FROM (
SELECT *
FROM (VALUES ('Id', 'FirstName', 'LastName')) t4(Id, FirstName, LastName)
UNION ALL
SELECT CONVERT(varchar(15), #t.Id)
,CONVERT(varchar(15), #t.FirstName)
,CONVERT(varchar(15), #t.LastName)
FROM #t) t3
FOR XML PATH ('Row'), ROOT('Table'))

SELECT t2.xmlTable
FROM t2

DROP TABLE #t


But this seems wildly cumbersome. Is there a more elegant solution for this? Specifically, can I do this without the UNION, perhaps using only XML PATH?

Continue reading...