CREATE XML SCHEMA COLLECTION WorkOrder
AS
'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
elementFormDefault="qualified" 
targetNamespace="urn:maintenance"
 xmlns="urn:maintenance">
  <xs:element name="WorkRequest">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Equipment" type="xs:string"/>
        <xs:element name="Reason" type="xs:string"/>
      </xs:sequence>
	  <xs:attribute name="timestamp" type="xs:string" use="required"/>
    </xs:complexType>
  </xs:element>
</xs:schema>
'



CREATE MESSAGE TYPE WorkInProcess
VALIDATION = VALID_XML WITH SCHEMA COLLECTION WorkOrder

CREATE MESSAGE TYPE WorkRequest
VALIDATION = WELL_FORMED_XML

CREATE MESSAGE TYPE WorkComplete
VALIDATION  = EMPTY


CREATE CONTRACT Maintenance
(
WorkInProcess SENT BY TARGET,
WorkRequest SENT BY INITIATOR,
WorkComplete SENT BY TARGET
)




CREATE QUEUE CentralMaintenanceQueue
WITH ACTIVATION (
PROCEDURE_NAME = CentralMaintenance,
MAX_QUEUE_READERS = 2, 
EXECUTE AS SELF ) 

CREATE SERVICE CentralMaintenanceService
ON QUEUE CentralMaintenanceQueue
([DEFAULT], Maintenance)



CREATE TABLE PendingWork
(
work XML
)
GO


CREATE
PROC CentralMaintenance
AS
WHILE 1 = 1
BEGIN
DECLARE @message VARBINARY(MAX);
DECLARE @conv UNIQUEIDENTIFIER;
DECLARE @group UNIQUEIDENTIFIER;
DECLARE @type NVARCHAR(256)
BEGIN TRAN
WAITFOR (
 GET CONVERSATION GROUP @conv
 FROM CentralMaintenanceQueue
 ), TIMEOUT 5000
IF @conv IS NULL
BEGIN
 ROLLBACK TRAN
 BREAK
END
-- get state
WHILE 1=1
BEGIN
WAITFOR (
RECEIVE TOP (1) 
 @message = message_body,	
 @group = conversation_group_id,
 @type = message_type_name
 FROM CentralMaintenanceQueue
 WHERE conversation_group_id = @conv
), TIMEOUT 5000;
IF @@ROWCOUNT = 1
-- process message
EXEC ProcessCentralMaintenance 
END
END
GO


CREATE 
PROC ProcessCentralMaintainence
(
@message VARBINARY(MAX),
@conv UNIQUEIDENTIFIER,
@group UNIQUEIDENTIFIER,
@type NVARCHAR(256)
)
AS
IF @type = 'DEFAULT'
BEGIN
--process default message
SELECT @message
END
IF @type = 'WorkRequest'
BEGIN
DECLARE @ts VARCHAR(MAX);
SET @ts = CAST(GETDATE() AS VARCHAR(MAX));
DECLARE @xmlMsg XML
SET @xmlMsg = @message
SET @xmlMsg.modify('insert (
attribute timestamp{sql:variable(''@ts'')}
) into (/*[1])');
SELECT @message, @conv;
SEND ON CONVERSATION @conv
Message Type WorkInProcess
(@message)
INSERT INTO PendingWork VALUES (@message)
END
IF @type = 
'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @conv
END
IF @type = 
'http://schemas.microsoft.com/SQL/ServiceBroker/Error '
BEGIN
-- handle error
SELECT @message
END
GO



CREATE QUEUE CentralMaintenanceQueue
WITH ACTIVATION (
PROCEDURE_NAME = CentralMaintenance,
MAX_QUEUE_READERS = 2, 
EXECUTE AS SELF ) 

CREATE SERVICE CentralMaintenanceService
ON QUEUE CentralMaintenanceQueue
([DEFAULT], Maintenance)

