CREATE PROCEDURE InsertHostedServicesFromXML(
    @sXML NTEXT
)
AS
begin transaction
    DECLARE @iDoc INT
    EXEC sp_xml_preparedocument @iDoc OUTPUT, @sXML
                       
    INSERT INTO dbo.HostedServices (ServiceImplementationClassName, ServiceType, ServiceBinding, BindingType,UseHttps,VirtualPath,Port,ServiceContract,LoadBalanceType,LoadBalanceAddress,HostedServiceID, Activated,MarkedAsFailover,ServiceFriendlyName,csUserKey,DefaultAddress,	InternalClientConfiguration,IsBaseAddress,ServiceHostID ,EndPointBehavior,Mex,HostName,OnlineMethod,OnlineParms,EndPointIdentity)
    SELECT ServiceImplementationClassName, ServiceType, ServiceBinding, BindingType,UseHttps,VirtualPath,Port,ServiceContract,LoadBalanceType,LoadBalanceAddress, HostedServiceID,Activated,MarkedAsFailover,ServiceFriendlyName,csUserKey,DefaultAddress,	InternalClientConfiguration,IsBaseAddress,ServiceHostID ,EndPointBehavior,Mex,HostName,OnlineMethod,OnlineParms,EndPointIdentity
    FROM OPENXML(@iDoc,'/HostedServices/dbo.HostedServices',2)
    WITH (	ServiceImplementationClassName varchar(200) 'ServiceImplementationClassName', 
			ServiceType int 'ServiceType', 
			ServiceBinding varchar(200) 'ServiceBinding', 
			BindingType varchar(50) 'BindingType', 
			UseHttps int 'UseHttps',
			VirtualPath varchar(200) 'VirtualPath', 
			Port varchar(50) 'Port', 
			ServiceContract varchar(200) 'ServiceContract',
			LoadBalanceType int 'LoadBalanceType',
			LoadBalanceAddress varchar(50) 'LoadBalanceAddress',
			HostedServiceID int 'HostedServiceID',
			Activated int 'Activated',
			MarkedAsFailover int 'MarkedAsFailover',
			ServiceFriendlyName varchar(200) 'ServiceFriendlyName',
			csUserKey int 'csUserKey',
			DefaultAddress varchar(200) 'DefaultAddress',
			InternalClientConfiguration varchar(200) 'InternalClientConfiguration',
			IsBaseAddress int 'IsBaseAddress',
			ServiceHostID int 'ServiceHostID',
			EndPointBehavior varchar(200) 'EndPointBehavior',
			Mex int 'Mex',
			HostName varchar(200) 'HostName',
			OnlineMethod varchar(100) 'OnlineMethod',
			OnlineParms varchar(200) 'OnlineParms',
			EndPointIdentity varchar(200) 'EndPointIdentity'
			);
	commit;
    EXEC sp_xml_removedocument @iDoc;
RETURN