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

Business Rules Engine – Conditions with Count Node

I needed to create a condition in the Business Rule Egine as follows:
If Field PDF is repeated more than 5 times, take the 5th record

So I needed in the conditions to count how many times the PDF field is repeated within the Xml file

I did the vfollowing:

1. Create a new vocabulary

2. Add New Definition
3. Select Xml Document Element or Attribute
4. Enter a Definition name, ex PDFCount
5. Browse for the Xsd file and select the Field
6. Update the XPath Field Value
    a. Add count(./  in front
    b. Add ) at the end
7. Select System.Double as Type
8. Select Perform “Get” operation
9. Enter a display name
10. Click Finish
11. Use the definition name in the Rule Conditions