SQL SERVER – XML – Split a Delimited String – Generate a Delimited String

SQL Server MVP and my very good friend Jacob Sebastian has written two wonderful articles about SQL Server and XML. I encourage to read this two articles to anybody who are interested in learning SQL and XML.

How to generate a Delimited String using FOR XML PATH

XQuery Lab 19 – How to parse a delimited string?

I have previously written article about operation of creating delimited string using COALESCE and no XML but as per telephonic conversation with Jacob COALESCE is only good for one column where as XML can do many more magics.

Jacob has already explained articles in detail so I suggest to read them carefully and digest. If you are not much big on XML, you do not have to spend time on learning this XML. Just take the XML script and use it for your need.

If you are wondering what about two article do, here is simple explanation. First article explains how to convert Sample 1 to Sample 2 and later one explains how to convert Sample 2 to Sample 1.
Sample 1:
/*

CompanyID CompanyCode
———– ———–
1 1
1 2
2 1
2 2
2 3
2 4
3 1
3 2

*/

Sample 2:
/*

CompanyID CompanyString
———– ————————-
1 1|2
2 1|2|3|4
3 1|2

*/

XML is very powerful and when combined with SQL Server it always unveils new side of RDBMS and DBMS.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

2 thoughts on “SQL SERVER – XML – Split a Delimited String – Generate a Delimited String

  1. Pinal, problem with for xml path(”) is if the field has xml character like ‘&’, the output will be ‘&’ how do we escape all xml invalid chars?

    Thanks in advance.

    • I found the answer on a linked post and adapted it to make it more readable:
      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(REPLACE((SELECT
      REPLACE(Color, ‘ ‘, ‘%20′) AS ‘data()’
      FROM @t c2
      WHERE c2.ColorID = c1.ColorID
      FOR XML PATH(”), TYPE).value(‘.[1]‘, ‘NVARCHAR(MAX)’), ‘ ‘, ‘|’), ‘%20′, ‘ ‘) AS ColorString
      FROM @t c1
      GROUP BY ColorID

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s