/*
	THIS IS FOR DEMO PURPOSES ONLY.
	NOTHING HERE IS INTENDED TO BE PRODUCTION QUALITY!
	
	For "Relational and XML" presentation, by Eric M. Wilson.
	See www.datazulu.com for more information.
	These examples are meant to work with SQL Server 2005 or higher.
*/
set nocount on
go



--------------------------------------------------
-- CREATE DB.

use master
go

-- drop database RelXmlDemo

if not exists (select 1 from sys.databases where [name] = 'RelXmlDemo')
	create database RelXmlDemo; -- just using default settings
go



--------------------------------------------------
-- CREATE HELPER FUNCTIONS.

use RelXmlDemo;
go

if exists (select 1 from sys.objects where [name]='Integers' and type='TF')
	drop function dbo.Integers;
go

create function dbo.Integers
(
    @Start bigint,
    @End bigint
)
returns @ret table(i bigint)
as
begin
/*	Adapted from http://www.sqlservercentral.com/articles/Datetime+Manipulation/61822/.
	For a .NET version, see www.datazulu.com.
*/
    with
    l0 as (select 1 as c union all select 1), --2 rows
    l1 as (select 1 as c from l0 as a, l0 as b),--4 rows
    l2 as (select 1 as c from l1 as a, l1 as b),--16 rows
    l3 as (select 1 as c from l2 as a, l2 as b),--256 rows
    l4 as (select 1 as c from l3 as a, l3 as b),--65536 rows
    l5 as (select 1 as c from l4 as a, l4 as b),--4294967296 rows
    num as (select row_number() over(order by c) as n from l5)

    insert into @ret(i)
    select n from num where n between @start and @end;
return;
end

/*
select * from dbo.Integers(5,50)
*/
go




if exists (select 1 from sys.objects where [name]='ListToTable' and type='TF')
	drop function dbo.Integers;
go

create function dbo.ListToTable
(
	@List		varchar(max)			--List of values delimited by the @Delimiter character.
,	@Delimiter	char(1)			= ','	--Single-character delimiter.
)
returns @tbl table (ItemNbr int, ItemValue varchar(max))
as
----------------------------------------------------------------------------------------------------
-- FUNCTION:	dbo.ListToTable
-- COPYRIGHT:	(c) 2002-2007, Eric M. Wilson, www.datazulu.com.
-- CREATED BY:	Eric M. Wilson
-- CREATED ON:	2002-01-11
-- TAB-SIZE:	4 (for best viewing)
--
-- RESULT SET ------------------------------------
--	Record per item within the delimited list.
--	[ItemNbr -Int], [ItemValue -SqlVariant]
--
-- NOTES AND ASSUMPTIONS -------------------------
--	1)	Ignores datatype. Returns varchar. Cast to appropriate type when selecting.
--	2)	Ignores spaces within delimiters. (Delimits first, then trims on insert.)
--	3)	Delimiter is never contained within a value. No escape character, etc.
----------------------------------------------------------------------------------------------------
begin
	select	@Delimiter		= coalesce(nullif(@Delimiter,''), ','); -- Replace empty or NULL delimiter with the default.

	declare	@intIndex		int
		  , @intPosition	int
		  , @strItem		varchar(max)
		  , @intItemNbr		int
	;
	select	@intIndex		= 1
		  , @intPosition	= 1
		  , @intItemNbr		= 0
	;
	while @intIndex > 0
	begin
		set @intIndex = charindex(@Delimiter, @List, @intPosition);
		if @intIndex > 0
			set @strItem = substring(@List, @intPosition, @intIndex - @intPosition);
		else
			set @strItem = substring(@List, @intPosition, len(@List));
		
		select	@strItem = ltrim(rtrim(@strItem))
			  , @intPosition = @intIndex + 1
			  , @intItemNbr = @intItemNbr + 1
		;
		insert @tbl (ItemNbr, ItemValue)
		values (@intItemNbr, @strItem);
	end

	return;	
end

/* select * from dbo.ListToTable('hi,there,cruel,world', ',');
*/

go
