Prashant: Read XML File in SQL Server

--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>

 <EmpID>1</EmpID>
 <Name>Prashant</Name>
 <City>Hupari</City>
 <Title>Sales</Title>


 <EmpID>2</EmpID>
 <Name>Suhas</Name>
 <City>Hupari</City>
 <Title>Marketing</Title>

 
 <EmpID>3</EmpID>
 <Name>Mahesh</Name>
 <City>Kolhapur</City>
 <Title>Purchase</Title>


 <EmpID>4</EmpID>
 <Name>Rajesh</Name>
 <City>Pune</City>
 <Title>Marketing</Title>


</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

Prashant

Wednesday, 6 April 2011

Read XML File in SQL Server

1 comment: