Get data in XML format from SQL

this return data in XML   format

select * from sales  for xml auto

Add unique Field in select statement

SELECT FieldName = IDENTITY(int, 1, 1),barcode,stockid  INTO NewTable FROM sales

select * from NewTable

Get Last Inserted Identity value

create table invoice(id int identity,amt numeric(18,2))

insert into invoice  select 25000

SELECT SCOPE_IDENTITY() 

Remove HTML Tag Function

create FUNCTION udfRemoveHTMLTag
(
@vrHTMLString VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @firstIndex INT
DECLARE @LastIndex INT
DECLARE @TotalLength INT
DECLARE @firstIndex2 INT
DECLARE @LastIndex2 INT
DECLARE @TotalLength2 INT

SET @firstIndex = CHARINDEX('<',@vrHTMLString)
SET @LastIndex = CHARINDEX('>',@vrHTMLString,CHARINDEX('<',@vrHTMLString))
SET @TotalLength = (@LastIndex - @firstIndex) + 1

WHILE @firstIndex > 0 AND @LastIndex > 0 AND @TotalLength > 0
    BEGIN
        SET @vrHTMLString = STUFF(@vrHTMLString,@firstIndex,@TotalLength,'')
        SET @firstIndex = CHARINDEX('<',@vrHTMLString)
        SET @LastIndex = CHARINDEX('>',@vrHTMLString,CHARINDEX('<',@vrHTMLString))
        SET @TotalLength = (@LastIndex - @firstIndex) + 1
    END
SET @firstIndex2 = CHARINDEX('&',@vrHTMLString)
SET @LastIndex2 = CHARINDEX(';',@vrHTMLString,CHARINDEX('&',@vrHTMLString))
SET @TotalLength2 = (@LastIndex2 - @firstIndex2) + 1

WHILE @firstIndex2 > 0 AND @LastIndex2 > 0 AND @TotalLength2 > 0
    BEGIN
        SET @vrHTMLString = STUFF(@vrHTMLString,@firstIndex2,@TotalLength2,'')
        SET @firstIndex2 = CHARINDEX('&',@vrHTMLString)
        SET @LastIndex2 = CHARINDEX(';',@vrHTMLString,CHARINDEX('&',@vrHTMLString))
        SET @TotalLength2 = (@LastIndex2 - @firstIndex2) + 1
    END
RETURN LTRIM(RTRIM(@vrHTMLString))
END

Recursive Store Procedure

create proc [dbo].[dba_1]
@agentcode varchar(20),
@level int,
@parentagent varchar(20),
@fromdate datetime,
@uptodate datetime
as
set nocount on
DECLARE @custtable TABLE
( Custcode varchar(20))

DECLARE @agenttable TABLE
( id int identity(1,1),agentcode varchar(20),parentagent_t varchar(20) )

insert into @custtable select na from cust where acode=@agentcode

declare @amt numeric(18,3),@ID  int,@agent_code_local varchar(20)

select @amt=sum(instlamt) from gstockch where na=@agentcode and childna in (selecT * from @custtable)
and dt between @fromdate and @uptodate insert into dba_table
select @agentcode,@amt,@level,@parentagent

set @level=@level+1

insert into @agenttable select na,@agentcode from agent where imme=@agentcode

declare @intloop int,@intuptoloop int,@parentagentttt varchar(20)
set @intloop=1
set @intuptoloop=(SELECT count(1) FROM @agenttable)
while @intloop<=@intuptoloop
begin
    select @agent_code_local=agentcode,@parentagentttt=parentagent_t from @agenttable where id=@intloop
    EXEC dba_1 @agent_code_local,@level,@parentagentttt,@fromdate,@uptodate
    set @intloop=@intloop+1
end