SQL Select FOR XML into Solr document

Question

I'm trying to get a SQL select statement to generate XML which conforms to the Solr standard.

Given a table like:

id | name
---------
 1 | one
 2 | two
 3 | three

I need a result which is like (with or without the root node):

<add>
  <doc>
    <field name="id">1</field>
    <field name="name">one</field>
  </doc>
  <doc>
    <field name="id">2</field>
    <field name="name">two</field>
  </doc>
  <doc>
    <field name="id">3</field>
    <field name="name">three</field>
  </doc>
</add>

Is it possible to generate that structure using a FOR XML query, or will I need an XSLT or some other mechanism to match that schema?

Answer 1

Here's a slightly different way using constructors.

DECLARE @sample TABLE
(
    [id]    int         NOT NULL,
    [name]  varchar(50) NOT NULL
);
INSERT INTO @sample ([id], [name])
SELECT 1, 'one' UNION ALL
SELECT 2, 'two' UNION ALL
SELECT 3, 'three';

SELECT
    CONVERT(xml, N'').query
    (N'
        <doc>
        {
            element field
            {
                attribute name {"id"},
                text{sql:column("id")}
            },
            element field
            {
                attribute name {"name"},
                text{sql:column("name")}
            }
        }
        </doc>
    ')
FROM
    @sample
FOR XML PATH(N''), ROOT(N'add');

* EDIT: Just thought of another way to do this (but still requires knowledge of the columns ahead of time) * Again, i'm uncertain of the performance implications of either approach.

SELECT
    (
        SELECT
            'id' AS [@name],
            [id] AS [data()]
        FOR XML PATH('field'), TYPE
    ) AS [*],
    (
        SELECT
            'name' AS [@name],
            [name] AS [data()]
        FOR XML PATH('field'), TYPE
    ) AS [*]
FROM
    @sample
FOR XML PATH(N'doc'), ROOT(N'add');

* UPDATE 2: Dynamic yet non-performant method inspired by Aaron Bertrand's comments *

This was a proof of concept of the method described in the post Aaron referenced in his comments. (It performs horribly on larger datasets)

-- Inspired by Aaron Bertrand's comment
WITH [cte_KVP]
AS
(
    -- Generating Key/Value pairs for columns in a table
    -- Courtesey of Mikael Eriksson (http://stackoverflow.com/questions/7341143/flattening-of-a-1-row-table-into-a-key-value-pair-table/)
    SELECT
        [T2].[N].value(N'local-name(.)', N'sysname')    AS [Key],
        [T2].[N].value(N'.', N'nvarchar(max)')          AS [Value],
        [T2].[N].value(N'../GROUP[1]', N'int')          AS [GROUP] -- 3. Used for to group the key/value pairs per row
    FROM
        (
            SELECT
                *,
                ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [GROUP] -- 1. Generating a simple "identity" value.
            FROM
                @sample
            FOR XML PATH(N'Row'), TYPE -- 2. Adding the 'Row' to the path separates each row, and allows us to backtrack via xpath to get the "GROUP" id
        ) AS [T1]([x])
    CROSS APPLY
        [T1].[x].nodes(N'Row/*') AS [T2]([N])
    WHERE
        [T2].[N].value(N'local-name(.)', N'sysname') <> N'GROUP'
)
SELECT
    [InnerNodes].[xml] AS [*]
FROM
    (
        -- Probably preferable to use a table of numbers here
        SELECT DISTINCT
            [GROUP]
        FROM
            [cte_KVP]
    ) AS [Numbers]([Number])
CROSS APPLY
    (
        -- Generating the xml fragment specified by OP
        SELECT
            [cte_KVP].[Key]     AS [@name],
            [cte_KVP].[Value]   AS [data()]
        FROM
            [cte_KVP]
        WHERE
            [cte_KVP].[GROUP] = [Numbers].[Number]
        FOR XML PATH(N'field'), ROOT(N'doc'), TYPE
    ) AS [InnerNodes]([xml])
FOR XML PATH(N''), ROOT(N'add');

Answer 2

It's probably not as "natural" as you want, and if you don't know the column names in advance, you'd have to build it dynamically, but this seems to produce the doc you're after:

SELECT 
    CONVERT(XML, '<field name="id">' + RTRIM(id) + '</field>'
    + '<field name="name">' + name + '</field>')
FROM dbo.[table]
FOR XML PATH(N'doc'), ROOT(N'add');

And here is a dynamic approach:

DECLARE
    @table NVARCHAR(512) = N'dbo.[table]',
    @sql   NVARCHAR(MAX) = N'';

SELECT @sql += '
    + ''<field name="' + name 
    + '">'' + CONVERT(NVARCHAR(MAX), ' 
    + QUOTENAME(name) + ') + ''</field>'''
    FROM sys.columns 
    WHERE object_id = OBJECT_ID(@table);

SET @sql = 'SELECT CONVERT(XML, ' + STUFF(@sql, 1, 4, '')
    + ') FROM ' + @table 
    + ' FOR XML PATH(N''doc''), ROOT(N''add'');';

PRINT @sql;
-- EXEC sp_executesql @sql;