CODEWITHSUNDEEP
sql-servert-sql
FrenkyB
FrenkyB

Reputation: 7217

Result from XML_PATH inside table

I would like to have results of @COLDEPARTMENTS inside table with one column.

Variable @COLDEPARTMENTS is taken from this query:

   DECLARE @COLDEPARTMENTS NVARCHAR(MAX)
   SELECT @COLDEPARTMENTS = STUFF((SELECT ',' + QUOTENAME(DEPA_KEY, '[')  FROM  @DEPARTMENTS_TBL FOR XML PATH('')), 1, 1, '')
                select @COLDEPARTMENTS

which is giving me results like this:

[120000003],[120000002],[140000001],[120000005],[120000021],[120000025]

What I would like to have now is table with one column named COLUMN_NAME with all those results inside. Is this possible?

Upvotes: 0

Views: 39

Answers (2)

Chanukya
Chanukya

Reputation: 5893

create function [dbo].[udf_splitstring] (@tokens    varchar(max),
                                         @delimiter varchar(5))
returns @split table (
  token varchar(200) not null )
as
  begin
      declare @list xml

      select @list = cast('<a>'
                          + replace(@tokens, @delimiter, '</a><a>')
                          + '</a>' as xml)

      insert into @split
                  (token)
      select ltrim(t.value('.', 'varchar(200)')) as data
      from   @list.nodes('/a') as x(t)

      return
  end

select * from udf_splitstring('[120000003],[120000002],[140000001],[120000005][120000021],[120000025]',',')

output

   [120000003]
   [120000002]
   [140000001]
   [120000005]
   [120000021]
   [120000025]

Upvotes: 1

Yashveer Singh
Yashveer Singh

Reputation: 1987

        Create FUNCTION [dbo].[SplitStrings]
                (
                   @List NVARCHAR(MAX),
                   @Delimiter NVARCHAR(255)
                )
                RETURNS TABLE
                WITH SCHEMABINDING AS
                RETURN
                  WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                                         UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
                       E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
                       E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
                       E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
                       cteTally(N)  AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
                                         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
                       cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                                         WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
                  SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
            FROM cteStart s;

and then use it like this 

     select * from   [dbo].[SplitStrings] ('1,2,3,4',',')

Upvotes: 1

Related Questions