SQL create split function and how to use function in sql

ALTER FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))      
returns @temptable TABLE (items varchar(8000))      
as      
begin      
    declare @idx int      
    declare @slice varchar(8000)      
     
    select @idx = 1      
        if len(@String)<1 br="br" is="is" nbsp="nbsp" null="null" or="or" return="return" tring="tring">     
    while @idx!= 0      
    begin      
        set @idx = charindex(@Delimiter,@String)      
        if @idx!=0      
            set @slice = left(@String,@idx - 1)      
        else      
            set @slice = @String      
         
        if(len(@slice)>0) 
            insert into @temptable(Items) values(@slice)      
 
        set @String = right(@String,len(@String) - @idx)      
        if len(@String) = 0 break      
    end  
return      
end 
GO


--- How to call the function

select * from [dbm].[Split] ('L196,L201,L197',',')

good blog about to write design doc

http://blog.slickedit.com/2007/05/how-to-write-an-effective-design-document/

good sites for travel planning

http://travel.nytimes.com/2012/01/08/travel/19-web-sites-for-travel-savings-in-2012.html

send email through SQL SP

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'WNweb01',
@recipients='DL@abc.com',
@body = @mbody,
@subject = 'my subject here'

learning site for school students

http://www.khanacademy.org/

this assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded, powershell

see below link. we have to create two config file.

http://tfl09.blogspot.com/2010/08/using-newer-versions-of-net-with.html

xml DeSerialization xsd.exe

good article

-------

http://blogs.msdn.com/b/yojoshi/archive/2011/05/14/xml-serialization-and-deserialization-entity-classes-with-xsd-exe.aspx

below is simple steps using VS 2010

1) open XML file that you want to deserialize in VS 2010

2) from VS 2010 menu > XML > Create Schema

3) It will create schema in you project save somewhere this .xsd file (in project)

4) go to VS command prompt

5) change path to your .xsd file (where you save above .xsd file)

6) type : xsd myschemafile.xsd /c (must you /c)

7) it will generate myschemafile.cs file in same location

8) type following code

using (FileStream xmlStream = new FileStream(@"C:\book.xml", FileMode.Open))

{

using (XmlReader xmlReader = XmlReader.Create(xmlStream))

{

XmlSerializer serializer = new XmlSerializer(typeof(catalog));

catalog deserializedStudents = serializer.Deserialize(xmlReader) as catalog;

foreach (var b in deserializedStudents.book )

{

Console.WriteLine("author : {0}", b.author);

Console.WriteLine("description : {0}", b.description);

Console.WriteLine("genre : {0}", b.genre);

Console.WriteLine("price : {0}", b.price);

Console.WriteLine("");

}

}

}