--1)--BUILD TABLE FROM XMLDOCUMENT----------------------------------
declare @DocHandle As INT
DECLARE @t as table
(
EmpID VARCHAR(100),
Name VARCHAR(100),
City VARCHAR(100),
Title VARCHAR(100)
)
EXEC SP_XML_PREPAREDOCUMENT @DocHandle OUTPUT,'<DocumentElement>
<P>
<EmpID>1</EmpID>
<Name>Prashant</Name>
<City>Hupari</City>
<Title>Sales</Title>
</P>
<P>
<EmpID>2</EmpID>
<Name>Suhas</Name>
<City>Hupari</City>
<Title>Marketing</Title>
</P>
<P>
<EmpID>3</EmpID>
<Name>Mahesh</Name>
<City>Kolhapur</City>
<Title>Purchase</Title>
</P>
<P>
<EmpID>4</EmpID>
<Name>Rajesh</Name>
<City>Pune</City>
<Title>Marketing</Title>
</P>
</DocumentElement>'
INSERT INTO @t SELECT * FROM OPENXML (@DocHandle, '/DocumentElement/P',2)
WITH (
EmpID VARCHAR(100),
Name VARCHAR(100),
City VARCHAR(100),
Title VARCHAR(100)
)
EXEC SP_XML_REMOVEDOCUMENT @DocHandle
select * from @t
--OR--
2)
USE [odLibrary]
GO
/****** Object: StoredProcedure [dbo].[sp_lib_Check_Student_and_Add_In_Library] Script Date: 10/31/2010 11:53:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[sp_lib_Check_Student_and_Add_In_Library]
(
@TransactionStatus BIGINT OUTPUT,
@strXML NVARCHAR(MAX)
)
AS
BEGIN
BEGIN TRANSACTION
--Declare local variables-------------------------------------
DECLARE @USERNAME AS VARCHAR(100)
DECLARE @USERCODE AS VARCHAR(100)
DECLARE @PPASSWORD AS VARCHAR(100)
DECLARE @SSTATUS AS BIT
DECLARE @CREATED_BY AS BIGINT
DECLARE @UPDATED_BY AS BIGINT
DECLARE @COMPANY_ID AS BIGINT
DECLARE @FULL_NAME AS NVARCHAR(500)
DECLARE @FIRST_NAME AS NVARCHAR(500)
DECLARE @MIDDLE_NAME AS NVARCHAR(500)
DECLARE @LAST_NAME AS NVARCHAR(500)
DECLARE @BATCH AS VARCHAR(50)
DECLARE @DocHandle AS INT
DECLARE @tblStudent TABLE
(
USERNAME VARCHAR(100),
USERCODE VARCHAR(100),
PPASSWORD VARCHAR(100),
SSTATUS BIT,
CREATED_BY BIGINT,
UPDATED_BY BIGINT,
COMPANY_ID BIGINT
)
DECLARE @tblStudentFinal TABLE
(
[CompanyId] BIGINT,
[LibDepositAmount] DECIMAL(18, 2),
[FirstName] VARCHAR(500),
[LastName] VARCHAR(500),
[Address] VARCHAR(1000),
[State] VARCHAR(500),
[City] VARCHAR(500),
[Email] VARCHAR(500),
[Notes] NTEXT,
[Batch] VARCHAR(50),
[CreatedBy] BIGINT,
[CreatedOn] DATETIME,
[UpdatedBy] BIGINT,
[UpdatedOn] DATETIME,
[Username] VARCHAR(100),
[Password] VARCHAR(100),
[Status] BIT,
[RoleId] INT,
[MiddleName] VARCHAR(500),
[Student_Code] VARCHAR(500)
)
--------------------------------------------------------------
----BUILD TABLE FROM XMLDOCUMENT----------------------------------
EXEC SP_XML_PREPAREDOCUMENT @DocHandle OUTPUT, @strXML
INSERT INTO @tblStudent SELECT * FROM OPENXML (@DocHandle, '/STUDENTS/ROW',2)
WITH (
USERNAME VARCHAR(100),
USERCODE VARCHAR(100),
PPASSWORD VARCHAR(100),
SSTATUS BIT,
CREATED_BY BIGINT,
UPDATED_BY BIGINT,
COMPANY_ID BIGINT
)
EXEC SP_XML_REMOVEDOCUMENT @DocHandle
--------------------------------------------------------------
--CREATE CURSOR---------------------------------------------
DECLARE cur_students CURSOR
LOCAL SCROLL STATIC
FOR SELECT USERNAME, USERCODE, PPASSWORD, SSTATUS, CREATED_BY, UPDATED_BY, COMPANY_ID FROM @tblStudent
--OPEN CURSOR
OPEN cur_students
FETCH NEXT FROM cur_students INTO @USERNAME, @USERCODE, @PPASSWORD, @SSTATUS, @CREATED_BY, @UPDATED_BY, @COMPANY_ID
WHILE @@FETCH_STATUS=0
BEGIN
--find users (firstname, middlename, lastname, batch)-------------
--SELECT DISTINCT([User_ID]),@BATCH=BATCH_CODE,@FULL_NAME=B.STUDENT_NAME FROM Users A
--INNER JOIN TRNMARKS B ON A.[USER_ID]=B.UNIQUE_ID
--WHERE [USER_ID]=@USERNAME
--split value------------------------------------------------------
--GET FIRST NAME
SELECT @FIRST_NAME = item FROM [dbo].fnSplit(@FULL_NAME,' ')
--GET MIDDLE NAME
SET @FULL_NAME = REPLACE(@FULL_NAME,@FIRST_NAME,'')
SELECT @MIDDLE_NAME = item FROM [dbo].fnSplit(@FULL_NAME,' ')
--GET THIRD NAME
SET @FULL_NAME = REPLACE(@FULL_NAME,@MIDDLE_NAME,'')
SELECT @LAST_NAME = item FROM [dbo].fnSplit(@FULL_NAME,' ')
--- First Check this student is already exist or not
DECLARE @retStudentId BIGINT
SET @retStudentId=0
SELECT @retStudentId = ISNULL(StudentId,0) FROM lib_studentmaster WHERE [StudentCode] = LTRIM(RTRIM(@USERCODE))
IF @retStudentId = 0
BEGIN
--INSERT VALUES--------------------------------------------------
INSERT INTO [lib_studentmaster]
(
[CompanyId]
,[LibDepositAmount]
,[FirstName]
,[LastName]
,[Address]
,[State]
,[City]
,[Email]
,[Notes]
,[Batch]
,[CreatedBy]
,[CreatedOn]
,[UpdatedBy]
,[UpdatedOn]
,[Username]
,[Password]
,[Status]
,[RoleId]
,[MiddleName]
,[StudentCode]
)
VALUES
(
@COMPANY_ID
,'0.0'
,@FIRST_NAME
,@LAST_NAME
,'Address'
,'Maharashatra'
,'Kolhapur'
,'vijeta@rediffmail.com'
,'Notes'
,@BATCH
,@CREATED_BY
,GETDATE()
,@UPDATED_BY
,GETDATE()
,@USERNAME
,@PPASSWORD
,@SSTATUS
,2
,@MIDDLE_NAME
,@USERCODE
)
END
ELSE
BEGIN
UPDATE lib_studentmaster SET [Status]=@SSTATUS, [FirstName]=@FIRST_NAME, [LastName]=@LAST_NAME , [MiddleName]=@MIDDLE_NAME
WHERE StudentId = @retStudentId
UPDATE Users SET IsAllowLibrary=@SSTATUS WHERE [Student_Code] = LTRIM(RTRIM(@USERCODE))
END
--------------------------------------------------------------
FETCH NEXT FROM cur_students INTO @USERNAME, @USERCODE, @PPASSWORD, @SSTATUS, @CREATED_BY, @UPDATED_BY, @COMPANY_ID
END
--DEALLOCATE AND CLOSE CURSOR
CLOSE cur_students
DEALLOCATE cur_students
-----------------------------------------------------------------
IF @@ERROR > 0
BEGIN
ROLLBACK TRANSACTION
RETURN @TransactionStatus
END
ELSE
BEGIN
COMMIT TRANSACTION
SET @TransactionStatus = 1
RETURN @TransactionStatus
END
END
