Post reply

FOR XML PATH Question

  • June 15, 2011 at 7:34 pm

    #237915

    Does anyone know how to turn a result set with four rows into one row comma delimited?

    For example, turn

    Value

    --------

    line1

    line2

    line3

    line4

    into

    Vaue

    ------

    line1, line2, line3, line4

    There isn't a common value between the four rows (such as a category id), and every other time I've concatenated rows, there has been some common element.

    I thought FOR XML PATH would be a good way to go. I've gotten all of the values to all be in one row, but with the XML tags. Is there a way to remove the tags?

    Thanks,

    Mike

    Mike Scalise, PMP
    https://www.michaelscalise.com

  • June 15, 2011 at 7:57 pm

    #1339416

    Like this:

    ; with cte (items) as

    ( select 'line1'

    union all select 'line2'

    union all select 'line3'

    union all select 'line4'

    )

    select ','+items

    from cte

    for xml path(''), type

  • June 16, 2011 at 6:38 am

    #1339616

    I never realized that concatenating a literal (e.g. ',' or even '') to the field name causes FOR XML PATH to remove the XML tags.

    Thanks.

    Mike

    Mike Scalise, PMP
    https://www.michaelscalise.com

  • June 16, 2011 at 6:49 am

    #1339628

    So the question is, why are the tags for JobStatus not hidden when all of the rest are?

    CREATE TABLE [dbo].[SisJobs](

    [Name] [nvarchar](128) NOT NULL,

    [LastRunDate] [datetime] NULL,

    [JobStatusCode] [int] NOT NULL

    )

    INSERT INTO SisJobs

    SELECT 'Test', GETDATE(), 231

    SELECT '' + Name, '' + CASE JobStatusCode

    WHEN 230 THEN 'Success'

    WHEN 231 THEN 'Running'

    WHEN 232 THEN 'Fail'

    END AS JobStatus,

    '' + LastRunDate

    FROM SisJobs

    ORDER BY Name

    FOR XML PATH (''), TYPE

    Mike

    Mike Scalise, PMP
    https://www.michaelscalise.com

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply