SQL Adapter – Generating a Schema

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.

Solution:

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   
    BizTalk Project.

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
     Stored Procedure;
    ex. SELECT Id, Name FRO Countries FOR XML AUTO