SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2014



Split String using XML - How to Convert or Split a Delimited String Values to Rows using T-SQL XML Commands


SQL developers are frequently required to parse or split delimited string values or parameters into a data rows form in every database system just as on MS SQL Server.
Using t-sql, sql developers or SQL Server database administrators probably developed user defined functions to handle splitting task.
I guess, you have prepared such udf functions for splitting and converting comma delimited varchar string values into a table row.

With the XML enhancements introduced to T-SQL developers and database administrators by the MS SQL Server 2005, you know we have used a lot the FOR XML PATH() concatenation method.
Just as concatenation, the opposite task splitting and parsing can also be implemented by using the new XML improvements in t-sql.
Here I will try to demonstrate a sample split sql code which parses comma delimited string inline. This method does not require a user defined function. So this is somehow a little bit more advanced than the regular parsing methods we used to code during our t-sql splitting tasks.





Split String using XML T-SQL Example


Here is a sql example to split delimited string using XML into table rows.

declare @xml xml, @s nvarchar(max)

select @s = N'a,b,c,d,e'

set @xml = N'<root><r>' + replace(@s,',','</r><r>') + '</r></root>'

select
  t.value('.','varchar(max)') as [delimited items]
from @xml.nodes('//root/r') as a(t)

We can still develop a t-sql user defined function to use in further parsing requirements.
In this sql split script with XML, the delimiter character can be defined in a more flexiable way.

CREATE FUNCTION Split
(
  @delimited nvarchar(max),
  @delimiter nvarchar(100)
) RETURNS @t TABLE
(
-- Id column can be commented out, not required for sql splitting string
  id int identity(1,1), -- I use this column for numbering splitted parts
  val nvarchar(max)
)
AS
BEGIN
  declare @xml xml
  set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'

  insert into @t(val)
  select
    r.value('.','varchar(max)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END
GO

SELECT * FROM dbo.Split(N'a,b,c,d,e', ',')

I hope, you are impressed with the T-SQL trick as I did. I liked split string using XML much and have already used string splitting using XML method in our production environments.


A Real Life Problem : An Other Example to Split String using XML


In the below sql problem, I believe sql developers or database administrators frequently experience, I will try to develop a select statement which will split values of a delimited character column value in a table and get the detail information by joining the ID values that we have just splitted.
In this sql example, database table Books has a column Authors which has the id's list as concatenated by "," character.
After string split by XML, we will use the each Id splitted to join the BookAuthors table on AuthorId column.

CREATE TABLE BookAuthors (
  AuthorId smallint,
  Author nvarchar(100)
)
GO
CREATE TABLE Books (
  BookId smallint identity(1,1),
  Book nvarchar(100),
  Authors varchar(100)
)
GO
INSERT INTO BookAuthors SELECT 1, N'Paul Turley'
INSERT INTO BookAuthors SELECT 2, N'Dan Wood'
INSERT INTO BookAuthors SELECT 3, N'Itzik Ben-Gan'
INSERT INTO BookAuthors SELECT 4, N'Lubor Kollar'
INSERT INTO BookAuthors SELECT 5, N'Dejan Sarka'

INSERT INTO Books
SELECT N'Beginning T-SQL with Microsoft SQL Server 2005 and 2008', '1,2'
INSERT INTO Books
SELECT N'Inside Microsoft SQL Server 2005: T-SQL Querying', '3,4,5'
GO
--select * from Books
--select * from BookAuthors

T-SQL Split String using XML

T-SQL Split String using XML

SELECT
  Books.BookId,
  Books.Book,
  BookAuthors.AuthorId,
  BookAuthors.Author
FROM Books
CROSS APPLY dbo.split(Books.Authors,',') split
INNER JOIN BookAuthors ON BookAuthors.AuthorId = split.val

T-SQL Split String using XML

In this t-sql split sample sql code, you can see how easy is to use the XML in split string processes.
Cross Apply enables the sql split function which uses XML, to join on string column on the main sql table and return a table which can be joined.

The same split string query can be implemented inline as shown in the below query.

SELECT
  Book.BookId,
  Book.Book,
  BookAuthors.AuthorId,
  BookAuthors.Author
FROM (
  SELECT
    BookId,
    Book,
    CAST('<r>' + REPLACE(Authors, ',', '</r><r>') + '</r>' AS XML) Authors
  FROM Books
) Book
CROSS APPLY (
  SELECT
    Authors.AuthorId.value('.', 'varchar(10)') AuthorId
  FROM Book.Authors.nodes('r') AS Authors(AuthorId)
) Split
INNER JOIN BookAuthors ON BookAuthors.AuthorId = Split.AuthorId

But if we examine the Include Actual Execution Plan for both split methods, using a split function using XML or directly splitting string using XML, I see that using a user-defined function which splits string using XML is better to use by comparing each Query Cost.






Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers









Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems