I have created a service broker that stores id's in the queue table. But the problem is when i want to get the id back in the stored procedure it's now formatted as xml. Because the service broker message is XML.
How can i just get the last Id each time because the trigger is fired after an update of a row.
Below my code =>
/****** Object: Trigger [dba].[TriggerCall] Script Date: 6/16/2015 2:55:57 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dba].[TriggerCall] ON [dba].[CallID] FOR UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @MessageBody XML
DECLARE @ID varchar(50)
-- Insert statements for trigger here
--get relevant information from inserted/deleted and convert to xml message
SET @MessageBody = (SELECT Id FROM inserted
FOR XML AUTO)
If (@MessageBody IS NOT NULL)
BEGIN
DECLARE @Handle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @Handle
FROM SERVICE [TestServiceInitiator]
TO SERVICE 'TestServiceTarget'
ON CONTRACT [TestContract]
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @Handle
MESSAGE TYPE [TestMessage](@MessageBody);
END
END
/****** Object: StoredProcedure [dbo].[usp_GetCall] Script Date: 6/16/2015 2:44:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetCall]
AS
BEGIN
DECLARE @message_type varchar(100)
DECLARE @dialog uniqueidentifier, @message_body XML;
Declare @Object as Int;
Declare @URL as varchar(255)
Declare @ResponseText as Varchar(8000);
Declare @ID as Varchar(38);
WHILE (1 = 1)
BEGIN -- Receive the next available message from the queue
WAITFOR (
RECEIVE TOP(1) @message_type = message_type_name,
@message_body = CAST(message_body AS XML),
@dialog = conversation_handle
FROM dbo.TestQueue ), TIMEOUT 500 if (@@ROWCOUNT = 0 OR @message_body IS NULL)
BEGIN
BREAK
END
ELSE
BEGIN
INSERT INTO [dbo].[testtabel]
([id]
,[callid],
[test])
VALUES
('111', '111', @message_body)
END
END CONVERSATION @dialog
END
END