save multiple record into table using xml in sql server

CREATE PROC [dbo].[UTIL_sp_GetAllUniqueNames]
@Str_Xml_In NVARCHAR(4000)
, @Str_Xml_Schema NVARCHAR(4000)
AS
BEGIN
DECLARE @Handle INT
--Create an internal representation of the XML document.
EXEC Sp_Xml_PrepareDocument @Handle OUTPUT, @Str_Xml_In
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT PresentationID, PresentationUniqueName FROM aS_tblPresentations WHERE PresentationID IN
(
SELECT * FROM OPENXML (@Handle, @Str_Xml_Schema, 1) WITH (PID INT)
)
EXEC Sp_Xml_RemoveDocument @Handle
END
GO

---------------------------



CREATE PROC [dbo].[as_sp_InsertContactsByPresentationID]
(
@p_PresentationID INT
, @p_XmlContactList NVARCHAR(4000)
, @p_Share BIT = 0
, @Message NVARCHAR(4000) = NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @m_XmlDocHandle INT, @m_MemberID INT
SELECT @m_MemberID = MemberID FROM aS_tblPresentations WHERE PresentationID = @p_PresentationID
IF((@m_MemberID IS NULL) OR (@m_MemberID = 0))
BEGIN
RAISERROR('Invalid PresentationID. Contacts could not be saved.', 16, 1)
RETURN
END
EXEC SP_XML_PREPAREDOCUMENT @m_XmlDocHandle OUTPUT, @p_XmlContactList
IF (@p_Share = 1)
BEGIN
INSERT INTO as_WS_tblShareEmails (EmailAddress, DisplayName, PresentationID)
SELECT *, @p_PresentationID FROM OPENXML (@m_XmlDocHandle, '/ROOT/Contact', 1) WITH (Email NVARCHAR(100), Name NVARCHAR(100))
WHERE Email NOT IN (SELECT EmailAddress FROM as_WS_tblShareEmails WHERE PresentationID = @p_PresentationID)
IF(Select Private from as_tblPresentations where PresentationID = @p_PresentationID)=1
BEGIN
INSERT INTO as_tblSharePrivatePresentations(PresentationID,ShareDateTime,EmailID)
SELECT @p_PresentationID,getdate(),* FROM OPENXML(@m_XmlDocHandle, '/ROOT/Contact', 1) WITH(Email NVARCHAR(100))
END

END
INSERT INTO aS_tblMemberContacts(ContactEmail,ContactName,MemberID)
SELECT *, @m_MemberId FROM OPENXML (@m_XmlDocHandle, '/ROOT/Contact', 1) WITH (Email NVARCHAR(100), Name NVARCHAR(100))
WHERE Email NOT IN
(
SELECT ContactEmail FROM aS_tblMemberContacts WHERE MemberID = @m_MemberID
)
EXEC SP_XML_REMOVEDOCUMENT @m_XmlDocHandle
UPDATE aS_tblPresentations SET ShareEmailMessage = @Message WHERE PresentationID = @p_PresentationID
SET NOCOUNT OFF
END
GO
---------------------


CREATE PROCEDURE [dbo].[cs_PostMetadata_Update]
@PostID int,
@MetadataList ntext,
@SettingsID int
AS
SET Transaction Isolation Level Read UNCOMMITTED
delete from cs_PostMetadata where PostID = @PostID
if @MetadataList is null --or len(ltrim(rtrim(@MetadataList))) = 0
return
declare @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @MetadataList
insert into cs_PostMetaData (PostID, MetaKey, MetaType, MetaValue)
select @PostID, M.[key], M.[type], M.[value]
from openxml(@idoc, '/entries/entry', 1)
with ([key] nvarchar(50), [type] nvarchar(50), [value] nvarchar(50)) as M
EXEC sp_xml_removedocument @idoc
GO
/****** Object: StoredProcedure [dbo].[cs_PostMetadata_Get] Script Date: 02/18/2010 23:25:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO



-------------------------------------

CREATE PROC [dbo].[aS_sp_SaveContacts]
(
@contactListXml VARCHAR(MAX)
, @UserId bigint
, @ContactList VARCHAR(2000) = ''
, @SendInvite BIT =null
, @PresentationID BIGINT = 0
)
AS
BEGIN
DECLARE @hdoc int
DECLARE @ContactName VARCHAR(50)

exec sp_xml_preparedocument @hdoc output , @contactListXml

INSERT INTO aS_tblMemberContacts
SELECT @UserId , contacts.contactname , contacts.contact
FROM OPENXML(@hdoc , '/contacts/contact' , 2)
WITH (
contactname VARCHAR(50) '@contactName',
contact VARCHAR(50) '@contactemail'
) AS contacts
where
not exists (SELECT * FROM aS_tblMemberContacts WHERE ContactEmail = contacts.contact AND MemberID = @UserId )
-- to exclude the emailids which has been unscribed globally -- Modified by Amrik - 001
and not exists (SELECT EmailID FROM as_tblGlobalUnsubscribe WHERE EmailID = contacts.contact)

IF(@SendInvite =1)
BEGIN
INSERT INTO aS_tblSendInvites
SELECT @UserId , contacts.contactname , contacts.contact,getdate()
FROM OPENXML(@hdoc , '/contacts/contact' , 2)
WITH (
contactname VARCHAR(50) '@contactName',
contact VARCHAR(50) '@contactemail'
) AS contacts
where
not exists (SELECT * FROM aS_tblSendInvites WHERE ContactEmail = contacts.contact AND MemberID = @UserId )
-- to exclude the emailids which has been unscribed globally -- Modified by Amrik - 001
and not exists (SELECT EmailID FROM as_tblGlobalUnsubscribe WHERE EmailID = contacts.contact)
END

EXEC SP_XML_REMOVEDOCUMENT @hdoc

SELECT C.ContactEmail,ISNULL(ISNULL(M.MemberID,A.MemberID),0) as memberid,ISNULL(E.PresentationShare,1)as presentationShare FROM aS_tblMemberContacts C
LEFT OUTER JOIN as_tblmembers M ON C.ContactEmail = M.EmailID
LEFT OUTER JOIN aS_tblAlternateEmails A ON C.ContactEmail = A.EmailAddress
--LEFT OUTER JOIN as_tblemailsettings E ON E.MemberID = M.MemberID
LEFT OUTER JOIN as_tblemailsettings E on (C.ContactEmail = E.emailid or E.MemberID = M.MemberID or E.MemberID = A.MemberID)
WHERE C.MemberID = @UserId--11259
AND C.ContactEmail IN (SELECT rtrim(ltrim(value)) FROM dbo.aS_fn_Split(@ContactList,','))

-- to exclude the emailids which has been unscribed globally -- Modified by Amrik - 001
and C.ContactEmail NOT IN (SELECT EmailID FROM as_tblGlobalUnsubscribe)
EXEC dbo.as_sp_PremiumShareCount @PresentationID,@@ROWCOUNT
END
GO

------------------------------------

as_sp_GetResetDetails 'test@authorgen.com','test',''*/
CREATE procedure [dbo].[as_sp_GetResetDetails]
@UserId VARCHAR(100) = Null ,
@Password VARCHAR(100) = Null,
@PIDS varchar(1000)
AS
BEGIN
DECLARE @Handle INT
DECLARE @MemberId INT
IF(NOT EXISTS(SELECT MemberID FROM aS_tblMembers WHERE EmailID = @UserId AND [Password] = @Password AND (UserType = 'A' OR UserType = 'I')))
BEGIN
RAISERROR('Login Failed', 16, 1)
RETURN
END
EXEC sp_Xml_PrepareDocument @Handle OUTPUT , @PIDS
SELECT PresentationID,Prefix,PresentationFileName,AmazonFileName from as_tblpresentations where presentationid in
(select id from OPENXML (@Handle,'/PIDS/PID') with (id INT)tbl_xml)
EXEC sp_xml_removedocument @handle
END