FOR XML PATH 和字符串连接 [英] FOR XML PATH and string concatenation

问题描述

我正在尝试创建一个连接硬编码字符串和使用 FOR XML PATH 创建的字符串的值.

I am trying to create a value that concatenates both hard coded strings and strings created using FOR XML PATH.

SUBSTRING(
(SELECT (', ' + [value]) 
 FROM [values]
 FOR XML PATH( '' )
), 3, 1000) +
' text in between my values ' +
SUBSTRING(
(SELECT (', ' + [otherValue]) 
 FROM [otherValues]
 FOR XML PATH( '' )
), 3, 1000)

因此，我希望在我的值 OtherValue1、OtherValue2、OtherValue3 之间使用 Value1、Value2、Value3 文本，但我得到的是一个空字符串.

So I would expect something like: Value1, Value2, Value3 text in between my values OtherValue1, OtherValue2, OtherValue3, but instead I get a blank string.

如果我取出子字符串(包括其中的查询)，我会得到介于两者之间的值，如果我只添加一个子字符串块，我会返回列表字符串.所以我无法弄清楚为什么与子字符串和 FOR XML PATH 查询连接会导致它返回一个空字符串.

If I take out the substrings (including the query inside it) I get the values in between fine, and if I only add one of the substring blocks by itself I get the the list string back. So I can't work out why having concatenation with the substring and FOR XML PATH queries causes it return an empty string.

推荐答案

我认为您最初的尝试已经差不多了.我一直使用相同的 FOR XML 方法来做这种类型的事情.下面的解决方案解决了您的确切示例，并且可以轻松适应其他目的:

I think your original attempt is almost there. I do this type of thing all the time using the same FOR XML approach. The solution below solves your exact example and can be easily adapted for other purposes:

DECLARE @delimiter NVARCHAR(10)
SET @delimiter = ', '

declare @values TABLE (
    [value] NVARCHAR(25)
)

declare @otherValues TABLE (
    [otherValue] NVARCHAR(25)
)

INSERT INTO @values VALUES ('Value1')
INSERT INTO @values VALUES ('Value2')
INSERT INTO @values VALUES ('Value3')

INSERT INTO @otherValues VALUES ('OtherValue1')
INSERT INTO @otherValues VALUES ('OtherValue2')
INSERT INTO @otherValues VALUES ('OtherValue3')

SELECT
    STUFF(
        (
            SELECT
                @delimiter + CAST([value] AS NVARCHAR(500)) + '' AS [text()]
            FROM
                @values
            FOR   
                XML PATH('')
        ),
        1,
        LEN(REVERSE(@delimiter)), -- Reverse the delimiter string in case it has trailing spaces; LEN() won't count those
        ''
    ) +
    ' text in between my values ' +
    STUFF(
        (
            SELECT
                @delimiter + CAST([otherValue] AS NVARCHAR(500)) + '' AS [text()]
            FROM
                @otherValues
            FOR   
                XML PATH('')
        ),
        1,
        LEN(REVERSE(@delimiter)), -- Reverse the delimiter string in case it has trailing spaces; LEN() won't count those
        ''
    )

这篇关于FOR XML PATH 和字符串连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！