Ever had the need to create an enumeration of values for a field or element within a BizTalk Schema? Did you know that you can store those values inside an Access database for synchronization with the enumeration values for the schema?

The BizTalk schema provides a CodeList feature that can be used to point to an Access 2003 database. When setting the appropriate properties, the values are pulled from the database and stored as enumerated values for the field/element.

In order to do this, create an Access database (MDB file extension) that has a table with a name that contains an underscore (e.g. Xml_OrderTypes). The table should contain at least 3 special columns: code, value, and desc (short for description). It can contain more columns than this; however, these column names are hard coded in the schema codelist feature to retrieve the enumerated values (see image below).

The code column allows you to specify a collection type that will then be used with an element/field's restricted, derivative CodeList property value. The value column will be the enumerated values added to the enumeration xs:restriction section of the schema. The desc column will contain the description of the each value, if populated, inside a dialog box that allows you to select which values are valid for a particular field/element within a schema. Populate the table with appropriate values for each column and save the database.

Next, create a standard schema and open its Schema node properties. There are a couple of properties that are used to enable the CodeList feature. First, set the CodeList Database path to a file path containing an Access 2003 database (see image below).

Next, set the Standard and StandardVersion properties to the name of the Access 2003 table containing the possible enumerated values. The algorithm is to set each respective property so that the concatenation of Standard and StandardVersion properties equals the Table Name within the Access 2003 database joined with an underscore. For example, if the Access 2003 database was named XmlSchemaTypes.mdb, and you have a table named SchemaTypes_v1, you should set the Standard property value to SchemaTypes and the StandardVersion property value to v1. You can use whatever naming scheme that you'd like, as long as it follows the algorithm (see image below).

Once you've set up the Access database and the schema standard properties, you can start creating fields/elements with their enumerated values from the Access database.

Next, create an element/field, set its Derived By property to Restriction, and type in the name of the collection type specified inside the Access 2003 database table for the CodeList property. For example, in the Access database image (first screenshot), the code column contains collection types for orderTypes, creditCardTypes, and cardTypes. If this element contains collections for OrderTypes, specify orderTypes as the value for the CodeList. When this property is set, you can successfully click the ellipsis (…) button next to it, invoking the display of a dialog box that shows all the possible enumerated values from the database. This gives you the opportunity to select all the values, or just a few for the element/field (see image below).

Select all the values you like for the allowable enumerations.

So how does it all work? After you've configured all the settings for the element/field, an annotation is added to the schema, allowing the BizTalk Schema editor to query the Access database table for the specified values. These values are added to the schema as an xs:enumeration-restriction, thus they are actually embedded into the schema with the values (see image below).

Whenever you need to update the values, reload the schema inside the designer, and reset the CodeList property.  This will reload the dialog, invoking the BizTalk editor to re-query the Access database.

Happy coding!!!