CREATE PROCEDURE InsertConnectedConfigServicesFromXML (
    @sXML NTEXT
)
AS
begin transaction
    DECLARE @iDoc INT
    EXEC sp_xml_preparedocument @iDoc OUTPUT, @sXML
                       
    INSERT INTO dbo.ConnectedConfigServices (ServiceImplementationClassName, ServiceContract, HostNameIdentifier, ClientConfiguration,BindingType,ConnectedConfigServiceID,Active,ServiceType,CurrentCSUserKey,VHostID,Address,OnlineMethod,OnlineParms,ServiceFriendlyName,LoadBalanceType,UseHttps)
    SELECT ServiceImplementationClassName, ServiceContract, HostNameIdentifier, ClientConfiguration,BindingType,ConnectedConfigServiceID,Active,ServiceType,CurrentCSUserKey,VHostID,Address,OnlineMethod,OnlineParms,ServiceFriendlyName,LoadBalanceType,UseHttps
    FROM OPENXML(@iDoc,'/ConnectedConfigServices/dbo.ConnectedConfigServices',2)
    WITH (ServiceImplementationClassName varchar(200) 'ServiceImplementationClassName', 
          ServiceContract varchar(200) 'ServiceContract', 
          HostNameIdentifier varchar(200) 'HostNameIdentifier', 
		  ClientConfiguration varchar(200) 'ClientConfiguration',
		  BindingType varchar(200) 'BindingType',
		  ConnectedConfigServiceID int 'ConnectedConfigServiceID',
	      Active int 'Active',
		  ServiceType int 'ServiceType',
		  CurrentCSUserKey int 'CurrentCSUserKey',
		  VHostID int 'VHostID',
		  Address varchar(200) 'Address',
		  OnlineMethod varchar(200) 'OnlineMethod',
		  OnlineParms varchar(200) 'OnlineParms',
		  ServiceFriendlyName varchar(200) 'ServiceFriendlyName',
		  LoadBalanceType int 'LoadBalanceType',
		  UseHttps int 'UseHttps');
	commit;
    EXEC sp_xml_removedocument @iDoc;
RETURN
