 | XML bulk insertion into SQL server |
| /****** Object: StoredProcedure [dbo].[sp_AddSubscriber] Script Date: 07/22/2007 13:59:41 ******/
| | 1:IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_AddSubscriber]') AND type in (N'P', N'PC'))
| | 2:DROP PROCEDURE [dbo].[sp_AddSubscriber]
| | 3:GO
| | 4:/****** Object: StoredProcedure [dbo].[sp_AddSubscriber] Script Date: 07/22/2007 13:59:42 ******/
| | 5:SET ANSI_NULLS ON
| | 6:GO
| | 7:SET QUOTED_IDENTIFIER ON
| | 8:GO
| | 9:CREATE procedure dbo.sp_AddSubscriber
| | 10:@InvitedBy INT
| | 11:, @FriendsList NTEXT
| | 12:AS
| | 13:
| | 14:-- Description:
| | 15:/* Stub: EXECUTE sp_AddSubscriber 28, '<root>
| | 16: <fr fname="Arindam" femail="arindam.tech@yahoo.co.in"/>
| | 17: <fr fname="Piter" femail="Piter@yahoo.co.in"/>
| | 18: </root>'
| | 19:*/
| | 20:
| | 21:/**/
| | 22:
| | 23:BEGIN
| | 24:
| | 25: SET NOCOUNT ON;
| | 26: DECLARE @hDoc INT
| | 27:
| | 28: BEGIN TRY
| | 29: BEGIN TRANSACTION subscriber
| | 30:
| | 31: /* prepare the xmldocument based on given xmlstring and returns a handel
| | 32: read more about sp_xml_preparedocument : http://msdn2.microsoft.com/en-us/library/aa260385(SQL.80).aspx
| | 33: */
| | 34: EXEC sp_xml_preparedocument @hDoc OUTPUT, @FriendsList
| | 35:
| | 36: INSERT
| | 37: T_InvitedFriends
| | 38: (F_InvitedBy
| | 39: , F_FriendEmail
| | 40: , F_FriendName)
| | 41: SELECT
| | 42: @InvitedBy
| | 43: , femail
| | 44: , fname
| | 45: FROM
| | 46: /* open the xmldocument based on given handle by */
| | 47: OPENXML(@hDoc, '/root/fr', 1)
| | 48: WITH /* The following declaration will be same as provided xml structure */
| | 49: (femail NVARCHAR(50), fname NVARCHAR(50))
| | 50:
| | 51: /* after execution remove the xmldocument from system */
| | 52: EXEC sp_xml_removedocument @hDoc
| | 53:
| | 54: COMMIT TRANSACTION subscriber
| | 55:
| | 56: END TRY
| | 57: BEGIN CATCH
| | 58: ROLLBACK TRANSACTION subscriber
| | 59: SELECT
| | 60: ERROR_MESSAGE() AS ErrorMessage
| | 61: , ERROR_SEVERITY() AS ErrorSeverity
| | 62: , ERROR_STATE() AS ErrorState
| | 63:
| | 64: END CATCH
| | 65:END
| | 66:GO
| | 67: |
|