Can this stored procedure loose data? "FOR XML EXPLICIT"

I have a problem with the stored procedure attached.
The sp will copy records from a table and set status to 3 (signal that record is retrieved).
The problem is that sometimes the status is updated but the data is not copied.
I am not familiar with the "FOR XML EXPLICIT" and wonder it this is OK to use in a transaction.
Is there a transaction problem in this stored procedure?
If so is there an easy way to avoid it or should we just rewrite the whole thing?

NB: The sp is fired from Biztalk, but I am not sure how.

Thank you for your efforts,
Anders


CREATE PROCEDURE [dbo].[B2B$GetOutboundMessages]  AS
BEGIN
      SET TRANSACTION ISOLATION LEVEL READ COMMITTED
     
      /* Read up to 10 messages from the queue */
      BEGIN TRANSACTION
            SELECT TOP 10 ID INTO #B2B_NAV_TEMP FROM dbo.[GSC$Outbound Message] WHERE Status=1 AND Folder IN ('RSN','X12','B2B') AND Type='DT2000' ORDER BY [Date]

            SELECT 1 AS Tag, NULL AS Parent,
                  'http://XX.XXXX.B2B/Navision/OutboundMessage' AS [Message!1!xmlns],
                  ID AS [Message!1!ID],
                  Folder AS [Message!1!Folder],
                  [Document No_] AS [Message!1!OutboundDocumentNo],
                  [Message] AS [Message!1!!CDATA]
                  FROM dbo.[GSC$Outbound Message]
                  WHERE ID IN (SELECT ID FROM #B2B_NAV_TEMP)
                  FOR XML EXPLICIT, BINARY BASE64
            
            UPDATE dbo.[GSC$Outbound Message] SET Status=3 WHERE ID IN (SELECT ID FROM #B2B_NAV_TEMP)
            
            DROP TABLE #B2B_NAV_TEMP
      COMMIT TRANSACTION
END

Open in new window

LVL 4
AndersHedegaardAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

elimesikaCommented:
HI
Well the Tag & Parent are not well formed
see
http://msdn.microsoft.com/en-us/library/ms189068.aspx
Alternatively, you can use a much simpler method described in
http://msdn.microsoft.com/en-us/library/ms189885.aspx
Anthony PerkinsCommented:
>>Well the Tag & Parent are not well formed <<
Why would you say that?
AndersHedegaardAuthor Commented:
Thank you so far but do you see any problems with the stored procedure regarding transaction?
Anthony PerkinsCommented:
I don't see any problems with your code.  You could write it more efficiently, but the Stored Procedure as written is sound.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndersHedegaardAuthor Commented:
More information could be good.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.