I recently had an interesting requirement from a client that entailed posting values in an XML field in SQL Server out to a message queue. This necessitated querying the XML field in the database based on certain parameters, and then looping through and posting each XML value into MSMQ. I won’t go into detail on MSMQ in this post, but I thought I would show how I looped through query values, since it is a useful pattern.
I started with a file name as an input parameter:
@FileName nvarchar(200) = NULL
I then added some variables: xml (set as varchar(max) since that’s what the external MSMQ assembly accepts), a count total that represents the number of rows in my query, a counter, and a table which holds the results of my query. For some reason I had to do two DECLARE statements to get the table variable to work in SQL Server 2005.
-- Sets up temp table to hold XML for loop
@tempTable table(id int identity(1,1),xml_msmq xml)
Finally I set up the loop. First I populate tempTable with XML using a query with various parameters. Since I already set up an autoincrement identity key (ID) in tempTable, we will have something to use for our loop. Then I set the counter variable to the MIN value for ID in tempTable, and orderCount to the MAX value. Finally, I use the WHILE expression to loop through all of the records in tempTable, posting the xml to another sproc (AddXMLMaxToMSMQ) which sends it out to MSMQ.
INSERT INTO @tempTable(xml_msmq) SELECT XML_Body FROM OtherTable WHERE ID = (SELECT ID FROM someOtherTable WHERE [FileName] = @FileName) AND [STATUS] = 2
SELECT @counter = MIN(id) FROM @tempTable
SELECT @orderCount = MAX(id) FROM @tempTable
WHILE (@counter <= @orderCount)
SET @xml = (SELECT xml_msmq FROM @tempTable WHERE ID = @counter)
--Call the sproc wich sends the xml to MSMQ
EXEC AddXMLMaxToMSMQ @xml, @serverName, @queueName, @queueType
--Increment the counter
SET @counter = @counter + 1