Generating a Schema for the BizTalk SQL Adapter

To call a Stored Procedure from BizTalk you need to use the SQL Adapter. This requires you add a special Schema to your BizTalk application, this Schema is generated by a wizard in Visual Studio (the wizard is installed as part of the BizTalk SDK).

This process isn't as straight forward as it should be an its easy to end up with a Schema where the value returned from the Stored Procedure is represented by a "Success" Element of "anyType which is useless.

Firstly, you must have a Stored Procedure that that returns a result set using "FOR XML AUTO". Secondly, when you run through the wizard you must add "XMLDATA" to the end of the Stored Procedure (and remove it after you have generated the Schema). The XMLDATA clause causes SQL Server to return the Schema for the result set (as well as the result). So, when you go through the wizard you must have a stored procedure looking something like this:

SELECT ColumnA, ColumnB FROM MyTable FOR XML AUTO, XMLDATA

After you have generated your schema, remove the "XMLDATA" clause to leave:

SELECT ColumnA, ColumnB FROM MyTable FOR XML AUTO

When you run through the wizard, most of the values you need to enter are obvious but when you get to the "Schema Information" screen, you need to change the "Port Type" to "Send".

When you get to the "Statement Information" screen, you need to enter valid values into the "Value" column (click the Value cell twice to enter data). Then hit "Generate", then hit "Next".

The SQL Adapter Wizard does not seem to support "FOR XML EXPLICIT", you get an error that says "the required attribute 'name' is missing". However, I would guess that if you can generate the boiler plate Schema using "FOR XML AUTO," you should be able to change your Stored Procedure and then manually edit the Schema generated by the Wizard to match what your XML EXPLICIT format.