SQL Adapter – Add generated item

I was trying to call a stored procedure using biztalk and to retrieve the response from the stored procedure.

I right clicked the project and clicked on “Add Generated item”.

After I set up everything correctly in the “Add Generated item” wizard and clicked “Generate” on the Statement information window,An error window popped up. “Failed o execute SQL Statement. Please ensure that the supplied syntax is correct.”

This Error occured because the response from the SQL database is not in XML format. To generate the response schema using “Add Generated Item”, biztalk must be able to retrieve an XML representation of the response schema.

To resolve this, simply add “XMLDATA” at the end of your SELECT statement in your store procedure.

SELECT ‘Things to return’ FOR XML AUTO, XMLDATA;

NOTE:  Make sure you remove ‘XMLDATA’ before you start your biztalk application. Otherwise the response you get from the SQL server might not be what you expect.