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

SQL SERVER - XML - Split a Delimited String - Generate a Delimited String xmlicon-800x734 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. Let us see how to Split a Delimited String.

How to generate a Delimited String is using FOR XML PATH

XQuery Lab 19 – How to parse a delimited string?

I have previously written article about operation of creating a 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.

Solarwinds

Jacob has already explained articles in detail so I suggest to read them carefully and digest. If you are not much bigger 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 articles do, here is a 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 (https://blog.sqlauthority.com)

Solarwinds
, , , , ,
Previous Post
SQLAuthority News – Tip of the Minute
Next Post
SQL SERVER – 2005 – Best Practices Analyzer (August 2008)

Related Posts

2 Comments. Leave new

  • 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.

    Reply
    • 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

      Reply

Leave a Reply

Menu