LISTING 4: VB Code That Retrieves an XML Document from SQL Server and Sends It to BizTalk Server

Private Sub cmdFulfillOrder_Click()

'Execute the p_xmlorder stored procedure and retrieve its XML output to an ADO Stream object,
 then submit the output to BizTalk.

Dim adoCnn As ADODB.Connection
Dim adoCmd As ADODB.Command
Dim adoPrm As ADODB.Parameter
Dim adoOutStr As ADODB.Stream
Dim guidSubmit
Dim btsInterchange As Interchange
   
Set adoCnn = New ADODB.Connection

With adoCnn
    .ConnectionString = "File Name=" & App.Path & 
	"\XML.udl;App=XMLExplicit"
    .Open
End With

Set adoCmd = New ADODB.Command

Set adoOutStr = New ADODB.Stream

adoOutStr.Open

With adoCmd
    .ActiveConnection = adoCnn
    .CommandText = "p_xmlorder"
    .CommandType = adCmdStoredProc
    Set adoPrm = .CreateParameter _
	("CustomerID", adChar, adParamInput, 5, txtCustomerID)
    .Parameters.Append adoPrm
    Set adoPrm = .CreateParameter _
	("OrderID", adInteger, adParamInput, , txtOrderID)
    .Parameters.Append adoPrm
    .Properties("Output Stream") = adoOutStr
    .Execute , , adExecuteStream
End With

adoCnn.Close
Set adoCnn = Nothing
Set adoCmd = Nothing

Set btsInterchange = New Interchange

guidSubmit = btsInterchange.Submit _
	(1, adoOutStr.ReadText, , , , , , _
	"Order Fulfillment Channel", , , False)

Set btsInterchange = Nothing
adoOutStr.Close
set adoOutStr = Nothing

End Sub