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!!!