CREATE PROCEDURE InsertServiceHostsFromXML (
    @sXML NTEXT
)
AS
begin transaction
    DECLARE @iDoc INT
    EXEC sp_xml_preparedocument @iDoc OUTPUT, @sXML
                       
    INSERT INTO dbo.ServiceHosts (ServiceHostID,HostName, ServiceImplementationClassName, HostEnvironment, HttpHostHeaderName,TcpHostHeaderName,ServiceType,IsWorkflowHost,ServiceBehaviorConfiguration)
    SELECT ServiceHostID, HostName, ServiceImplementationClassName, HostEnvironment, HttpHostHeaderName,TcpHostHeaderName,ServiceType,IsWorkflowHost,ServiceBehaviorConfiguration
    FROM OPENXML(@iDoc,'/ServiceHosts/dbo.ServiceHosts',2)
    WITH (ServiceHostID int 'ServiceHostID',
		  HostName varchar(200) 'HostName', 
          ServiceImplementationClassName varchar(200) 'ServiceImplementationClassName', 
          HostEnvironment int 'HostEnvironment', 
		  HttpHostHeaderName varchar(200) 'HttpHostHeaderName',
		  TcpHostHeaderName varchar(200) 'TcpHostHeaderName',
	      ServiceType int 'ServiceType',
		  IsWorkflowHost int 'IsWorkflowHost',
		  CurrentCSUserKey int 'CurrentCSUserKey',
		  VHostID int 'VHostID',
		  ServiceBehaviorConfiguration varchar(200) 'ServiceBehaviorConfiguration');
	commit;
    EXEC sp_xml_removedocument @iDoc;
RETURN
