Subscribe To

Subscribe to SQL Server and XML by Email

Thursday, August 14, 2008

How to generate a Delimited String using FOR XML PATH

Moved to FOR XML PATH - How to generate a Delimited String using FOR XML PATH?


  1. Dear Jacob,
    Nice article,
    But i am facing problem to run the query what u write here.
    I got error msg Near XML PATh.
    Can u tell me what i have to do before run the query.
    as XML PATH is not working on SSMS windows.

  2. Ajeet,
    could you post the exact error message you are getting?

    Also make sure that you are connected to an instance of SQL Server 2005 or above. Run "SELECT @@VERSION" and see what it returns.

  3. I have a rows of color names and sizes which may includes spaces in the name (Soft Pink), when this script runs it makes:

    row 1: Soft Pink
    row 2: Dark Blue

    Turn into:
    row 1: Soft, Pink, Dark, Blue

    it should return:
    row 1: Soft Pink, Dark Blue

    How can I fix the script to achieve this? Thanks-

  4. Justin,
    You can achieve it by using the following trick:

    DECLARE @t TABLE (ColorID INT, Color VARCHAR(50))
    INSERT INTO @t (ColorID, Color) SELECT 1, 'Soft Pink'
    INSERT INTO @t (ColorID, Color) SELECT 1, 'Dark Blue'
    INSERT INTO @t (ColorID, Color) SELECT 2, 'Sky Blue'
    INSERT INTO @t (ColorID, Color) SELECT 2, 'Ocean Blue'

    SELECT ColorID,
    REPLACE(Color, ' ', '%20') AS 'data()'
    FROM @t c2
    WHERE c2.ColorID = c1.ColorID
    FOR XML PATH('')), ' ', '|'), '%20', ' ') AS ColorString
    FROM @t c1
    GROUP BY ColorID

    ColorID ColorString
    ----------- --------------------------------------------------
    1 Soft Pink|Dark Blue
    2 Sky Blue|Ocean Blue

  5. Thanks for your reply, I just recieved it. I did almost the same thing you suggested except I just did instead of this

    REPLACE(Color, ' ', '%20') AS 'data()'

    (Color + '|') AS 'data()'

    Does the same thing. Thanks again for your help, this is a wonderful script for what I have to do for 100,000's of records.

  6. Oh yeah, then just replace | with ,!


About Me
Jacob Sebastian
* XQuery Tutorials
My Links
SQL Server White Papers
SQL Server 2008
My Articles
XML Workshop RSS Feed
Contact me
Free Hit Counter
Web Site Hit Counters
SQL Server Bloggers
Blog Directories
blogarama - the blog directory Programming Blogs - BlogCatalog Blog Directory
Copyright Jacob Sebastian