Biztalk 2006 strange behaviour caused by incorrect xpath

This weekend I ran into some strange behaviour.
I have an orchestration in which I do the following:

  • Write a record in a database

  • Read the ID of the record

  • Send an email with that ID

  • Wait for a reply

  • Update the record in the database

It took me some time to get the record in the database and return the ID. There is a small note in the tutorial which says to use FOR XML RAW in case only one or two rows are returned.
To read the ID from the response message, I took the xpath query Biztalk shows in the schema editor and put it in an expression shape.

After putting in some trace logging I tested the orchestration. What I saw was that the record was correctly inserted in the database and the record ID was returned in the response message (I put in a send shape to verify what I got returned from the SQL adapter).

In the Debugview tool I noticed that the ID wasn’t extracted correctly from the response and that the orchestration halted just before the ‘Send email’ part. Also in the eventlog I saw the following error message: Input string was not in a correct format. This error would mean I tried to convert a NULL value, but I didn’t use any convert in the expression. Probably it was done implicitly caused by the incorrect xpath query.

To figure out where the orchestration exactly halted, I launched the orchestration debugger. I was very surprised to find out that the orchestration debugger told me it stopped on the receive shape from the SQL adapter, while I had an output message on the send shape just after that receive!!

I couldn’t figure out why the logging showed me that the orchestration ran further than the orchestration debugger showed.

I tried all kinds of xpath queries and I found the correct one.
After updating my expression shape with the correct xpath, the orchestration finally ran as expected.