How to update many records from Biztalk using a Stored Proc.

I thought I "knew how" but I never actually did it until today.

In this case, we are passing a large "chunk" of data to a web service,
it calls a DLL and does various updates. But if we get a SOAP exception,
we wanted to update the status in the original Transaction Table to indicate a failure. (For example: Status=1 Biztalk ready to pick, Status=2 Biztalk picked,
Status=3 SOAP Failure, Status=4 Success).

This blog: http://www.sqlxml.org/faqs.aspx?faq=101 saved the day
with a little "feature" that was biting me. Biztalk always uses a
default namespace, none of the example I have seen showed how to handle this.

STORED PROCEDURE

ALTER PROCEDURE dbo.spBTUpdatePATransactionStatus
@XML as varchar(8000)
AS
DECLARE @XMLDocHandle int
DECLARE @Xpath varchar(100)
-- Note the third parm to sp_xml_preparedocument,
-- the key to getting this to work with default namespaces
EXEC sp_xml_preparedocument @XMLDocHandle output, @XML,
'<root xmlns:a="http://tempuri.org/"/>'


/* must use OpenXML to receive and handle block of XML from Biztalk */

/* The select is for testing only - make sure select works before you
do the update, then remove the select */

select * from
OPENXML(@XMLDocHandle,'/*[local-name()="RefIDStatusTableArray" and namespace-uri()="http://tempuri.org/"]/*[local-name()="refIDStatusTableItem" and namespace-uri()="http://tempuri.org/"]/*[local-name()="RefIDStatusTable" and namespace-uri()="http://tempuri.org/"]',2)

WITH
(RefID varchar(50) 'a:RefID',
TransactionID varchar(50) 'a:TransactionID',
Status int 'a:Status')
XmlTableAlias

UPDATE PA_Transaction
SET PA_Transaction.Status = XmlTableAlias.Status
FROM OPENXML(@XMLDocHandle,'/*[local-name()="RefIDStatusTableArray" and namespace-uri()="http://tempuri.org/"]/*[local-name()="refIDStatusTableItem" and namespace-uri()="http://tempuri.org/"]/*[local-name()="RefIDStatusTable" and namespace-uri()="http://tempuri.org/"]',2)

WITH (RefID varchar(50) 'a:RefID',
TransactionID bigint 'a:TransactionID',
Status int 'a:Status') XmlTableAlias

WHERE XmlTableAlias.TransactionID = PA_Transaction.TransactionID

EXEC sp_xml_removedocument @XMLDocHandle



To Test, run this in SQL Query Analyzer:

DECLARE @RC int

DECLARE @XML varchar(8000)

SELECT @XML = '<RefIDStatusTableArray xmlns="http://tempuri.org/"><refIDStatusTableItem>
<RefIDStatusTable><RefID>111</RefID><TransactionID>1</TransactionID>
<Status>12</Status></RefIDStatusTable>
<RefIDStatusTable><RefID>222</RefID><TransactionID>2</TransactionID>
<Status>13</Status></RefIDStatusTable>
<RefIDStatusTable><RefID>333</RefID><TransactionID>3</TransactionID>
<Status>14</Status></RefIDStatusTable>
</refIDStatusTableItem></RefIDStatusTableArray>'

EXEC @RC = spBTUpdatePATransactionStatus @XML
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: spBTUpdatePATransactionStatus'
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine

TO PASS DATA FROM ORCHESTRATION

To pass data in the orchestration, include something like this in a construct statement:
vXmlDoc = msgWhatever;
msgToStoredProc.XML = vXmlDoc.OuterXml;

NOTE: if you want the stored proc to be synchronous, I think you have to return a response. You simply add this to the end of the stored proc:

Select 1 as 'ResultCode'
for xml auto, elements --, xmldata

You could also return the number of rows affected or something else.