使用 FOR XML 进行行串联，但具有多个列？

我经常使用如下查询：

SELECT *
FROM   ThisTable
       OUTER APPLY (SELECT (SELECT SomeField + ' ' AS [data()]
                            FROM   SomeTable
                            WHERE  SomeTable.ID = ThisTable.ID
                            FOR XML PATH ('')) AS ConcatenatedSomeField) A 

我经常想从这个表中获取多个串联的串联字段，而不是只有一个。从逻辑上讲，我可以这样做：

SELECT *
FROM   ThisTable
       OUTER APPLY (SELECT (SELECT SomeField + ' ' AS [data()]
                            FROM   SomeTable
                            WHERE  SomeTable.ID = ThisTable.ID
                            FOR XML PATH ('')) AS ConcatenatedSomeField) A
       OUTER APPLY (SELECT (SELECT SomeField2 + ' ' AS [data()]
                            FROM   SomeTable
                            WHERE  SomeTable.ID = ThisTable.ID
                            FOR XML PATH ('')) AS ConcatenatedSomeField2) B
       OUTER APPLY (SELECT (SELECT SomeField3 + ' ' AS [data()]
                            FROM   SomeTable
                            WHERE  SomeTable.ID = ThisTable.ID
                            FOR XML PATH ('')) AS ConcatenatedSomeField3) C 

但当需要更新任何内容时，它看起来很糟糕并且容易出错； SomeTable 通常是一长串连接表，因此反复获取相同的表也可能会对性能产生影响。

有一个更好的方法吗？

Thanks.

你可以做这样的事情。此查询不是立即将 XML 值发送到字符串，而是使用 TYPE 关键字返回一个 xml 类型对象，然后可以查询该对象。这三个查询函数在 xml 对象中搜索 Somefield 元素的所有实例，并返回仅包含这些值的新 xml 对象。然后 value 函数去掉值周围的 xml 标签并将它们传递到 varchar(max)

SELECT  ThisTable.ID
       ,[A].query('/Somefield').value('/', 'varchar(max)') AS [SomeField_Combined]
       ,[A].query('/Somefield2').value('/', 'varchar(max)') AS [SomeField2_Combined]
       ,[A].query('/Somefield3').value('/', 'varchar(max)') AS [SomeField3_Combined]
FROM    ThisTable
        OUTER APPLY (
                     SELECT (
                             SELECT SomeField + ' ' AS [SomeField]
                                   ,SomeField2 + ' ' AS [SomeField2]
                                   ,SomeField3 + ' ' AS [SomeField3]
                             FROM   SomeTable
                             WHERE  SomeTable.ID = ThisTable.ID
                            FOR
                             XML PATH('')
                                ,TYPE
                            ) AS [A]
                    ) [A]