[Solved] Convert csv values into table rows using xml. Can anyone please explain how below mentioned queries will work

Question

Here is my explanation for this script

following creates a sample database table containing an object (name) and its dependends on an other field (DependsOnCSV) seperated by comma

CREATE TABLE tbl (Name VARCHAR(100),DependsOnCSV VARCHAR(100))

Following code populates above table with sample data. This is a new syntax for many developers. If you are working with a prier version of SQL2012 you might have to convert it into INSERT INTO .. SELECT … for each row

INSERT INTO tbl
VALUES
    ('a','b,c'),
    ('b','d'),
    ('c',''),
    ('d',''),
    ('e','g'),
    ('f','b,e,a,g'),
    ('g',''),
    ('h','a')

Below function is used to split string values in SQL. You can find many similar functions. SQL Server 2016 is being shipped with a built-in split function

CREATE FUNCTION Split
(
  @delimited nvarchar(max),
  @delimiter nvarchar(100)
) RETURNS @t TABLE
(
  id int identity(1,1),
  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(5)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END
GO

This SELECT statements returns objects with each of their dependens by splitting the DependendOnCVS column by “,” comma

SELECT
  tbl.Name,
  split.val AS [DependsOn-Rows]
FROM tbl AS tbl
CROSS APPLY dbo.Split(tbl.DependsOnCSV,',') split

I hope the above code is readable now

Accepted Answer