SQL 选择 FOR XML 到 Solr 文档中

Question

我正在尝试获取 SQL select 语句来生成符合 Solr 标准的 XML。

给定一个像这样的表：

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

我需要一个类似于（有或没有根节点）的结果：

<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>

是否可以使用 FOR XML 查询生成该结构，或者我需要 XSLT 或其他一些匹配该模式的机制？

Answer 1

这是使用构造函数的稍微不同的方法。

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');

* 编辑：只是想到了另一种方法来做到这一点（但仍然需要提前了解列）*
同样，我不确定这两种方法对性能的影响。

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');

* 更新 2：受 Aaron Bertrand 评论启发的动态但非性能方法 *

这是 Aaron 在评论中引用的帖子中描述的方法的概念证明。 （它在较大的数据集上表现非常糟糕）

-- 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

它可能不像您想要的那么“自然”，如果您事先不知道列名称，则必须动态构建它，但这似乎会产生您想要的文档：

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');

这是一个动态的方法：

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;

Answer 3

我假设您正在使用 .NET 进行编程，因为您使用的是 SQL Server。如果您考虑过使用 SolrNet 客户端将文档加载到 Solr 服务器中？