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

Question

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?

Answer 1

Please try the following solution based on SQL Server's native XQuery.

It is generic regardless how many columns table has.

Column names are not hard-coded anywhere.

Notable points:

• First, inner SELECT is composing behind the scenes a raw XML (please see below).
• Second, XQuery .query() method is composing the desired output "visually" by using XQuery FLWOR expressions.
• XPath expression /root/r[1]/* is limiting FLWOR just to one iteration to retrieve column names dynamically by calling XQuery local-name() function.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, FirstName VARCHAR(30), LastName VARCHAR(30));
INSERT INTO @tbl (FirstName, LastName) VALUES
('John', 'Doe'),
('Mary', 'Pollack');
-- DDL and sample data population, end

SELECT (
    SELECT * FROM @tbl
    FOR XML PATH('r'), TYPE, ROOT('root')
).query('<Table><Row>{
    for $x in /root/r[1]/*
    return <Column>{local-name($x)}</Column>
}</Row>{
    for $x in /root/r
    return <Row>{
        for $y in $x/*
        return <Column>{data($y)}</Column>}</Row>
}
</Table>');

Raw XML

<root>
  <r>
    <id>1</id>
    <FirstName>John</FirstName>
    <LastName>Doe</LastName>
  </r>
  <r>
    <id>2</id>
    <FirstName>Mary</FirstName>
    <LastName>Pollack</LastName>
  </r>
</root>

Output

<Table>
  <Row>
    <Column>id</Column>
    <Column>FirstName</Column>
    <Column>LastName</Column>
  </Row>
  <Row>
    <Column>1</Column>
    <Column>John</Column>
    <Column>Doe</Column>
  </Row>
  <Row>
    <Column>2</Column>
    <Column>Mary</Column>
    <Column>Pollack</Column>
  </Row>
</Table>