使用 FOR XML 进行行连接，但具有多列? [英] Row concatenation with FOR XML, but with multiple columns?

问题描述

我经常使用以下查询:

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

我经常想从这个表中得到多个串联的串联字段，而不是一个.我可以合乎逻辑地这样做:

I often want to get multiple concatenated concatenated fields from this table, instead of just one. I could logically do this:

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 通常是一长串连接表，因此它也可能会影响性能，一遍又一遍地获取相同的表.

But it looks crappy and error prone when anything needs to be updated; also SomeTable is often a long list of joined tables so it could also have performance implications getting the same tables over and over.

有没有更好的方法来做到这一点?

Is there a better way to do this?

谢谢.

推荐答案

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

You could do something like this. Instead of immediately sending the XML value to a string, this query uses the TYPE keyword to return an xml type object which can then be queried. The three query functions search the xml object for all instances of the Somefield element and return a new xml object containing just those values. Then the value function strips out the xml tags surrounding the values and passes them into a 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]

这篇关于使用 FOR XML 进行行连接，但具有多列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！