FOR XML PATH and string concatenation

问题:

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)

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

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 cant work out why having concatenation with the substring and FOR XML PATH queries causes it return an empty string.... HELP!

回答1:

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

回答2:

You don't need to use XML functionality to create a list of things like you want. It really wasn't built to do that: it was built for generating XML. :)

Try this. I use this technique a lot:

DECLARE @myList nvarchar(MAX)

SELECT
    @myList = COALESCE(@myList + ', ', '') + MyColumn
    FROM MyTable;

For future readers, please note that using ORDER BY with this technique does not guarantee that the values will end up ordered in the output; see here.

回答3:

I guess you got a blank string because SUBSTRING I am not sure why you use SUBSTRING to get a result like "Value1, Value2, Value3 text in between my values OtherValue1, OtherValue2, OtherValue3" try this.

SELECT
-- ISNULL( here in case [values] is empty table
    ISNULL(STUFF((
        SELECT ', ' + [value]
-- should be ', ' + NULLIF(value,'') or ', ' + ISNULL(value,'') depends on what you need
-- there is no column name for ', ' + [value], so AS [text()] is not need here
        FROM [values]
        FOR XML PATH(''),TYPE
    ).value('text()[1]','nvarchar(max)'), 1, 2, ''), '') +
-- you can replace PATH(''),TYPE).value('text()[1]','nvarchar(max)')
-- with PATH('')) it will be faster but if the values contain & > < etc. chars,
-- they will result in &amp &gt; &lt;  
    ' text in between my values ' +
    ISNULL(STUFF((
        SELECT ', ' + [otherValue] 
        FROM [otherValues] WHERE [otherValues]<>''
        FOR XML PATH(''),TYPE
    ).value('text()[1]','nvarchar(max)'), 1, 2, ''), '')