XML Import

CREATE PROCEDURE [TestingSP].[prc_Testing_INSERT_XML]
@intClientID INT,
@CurrencyXML XML
AS
DECLARE @TempTable TABLE (FirstName NVARCHAR (256), LastName NVARCHAR (256), Email NVARCHAR(256));
BEGIN


INSERT INTO @TempTable(FirstName, LastName, Email)
SELECT MliFirstName = T.Item.query('firstname').value('.', 'NVARCHAR(256)'),
MliLastName = T.Item.query('lastname').value('.', 'NVARCHAR(256)'),
MliEmail = T.Item.query('email').value('.', 'NVARCHAR(256)')
FROM @CurrencyXML.nodes('shq-mailinglist/person') AS T(Item)

BEGIN TRY
IF EXISTS (SELECT * FROM @TempTable)
BEGIN

INSERT INTO Testing.ImpMailingList(ClID, ImlFirstName,
ImlLastName, ImlEmail)
SELECT @intClientID, tt.FirstName, tt.LastName, tt.Email
FROM @TempTable tt;
END

RETURN 1;
END TRY
BEGIN CATCH

INSERT INTO Testing.ApplicationError(ClID, AerENo, AerESeverity, AerEState, AerEProcedure, AerELine, AerEMessage)
VALUES(NULL, ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE());

RETURN -1;
END CATCH

END