When using the Stored Procedures in SQL Server and BizTalk, we need to add the XSD to the project. When using the Add Adpater MetaData feature of BizTalk (Add –> Add Generated Items…) BizTalk generates the schema, but the result is equal to the Success element and is of atype AnyType.
This behavior is not the one we want as we want to use the return values.
1. First create the Store Procedure
ex. SELECT Id, Name FROM Countries FOR XML AUTO, XMLDATA
Be sure to add FOR XML AUTO, XMLDATA at the end.
2. Run the Add Adapter MetaData wizard, within Visual Studio and your
3. At the Statement Information screen, you need to enter valid values
into the Value column
4. Click the Generate button
5 Finish the Wizard, you will see that now the XSD will contain the
structured results and not anymore the AnyType element.
6. After the XSD is generate, remove the XMLDATA clause from the
ex. SELECT Id, Name FRO Countries FOR XML AUTO