UPDATE!!!
Do not use this method if you are planning on doing any bulk loading. There is a 10,000 variable limitation that is reported through the BizTalk Event Log entries but is actually a SQL Server design limitation.
and
I recently was working for a company who needed to store data coming in from the 837 claim into a database.
Experiencing many poorly designed databases to store claim data before, I create a database structure that will capture what the client was looking for, but also allow easy implementation of additional values to be captured into the database as use of this process increased. Now my goal/issue was to get BizTalk to take the 837 data and insert all of the data into the various tables.
For the purposes of this blog, I have simplified the example, but the concepts are the same.
The first thing I have done is created a psuedo837Claim schema, that looks somewhat like the 837 schema looks like. In the picture, I have marked what is repeatable.
The next thing I have done is created the database structure:
So now I need to start generating the schemas that represented the web I have just created. We will start by following Steve Kaufman's guidance here and creating an at-identity schema that we will use throughout the process. Once I have completed that, I generate the Header schema from the Header table. I select all of the columns (as I will for all but the end, but we will get to that later).
Then for the purposes of this blog, I will import the Application table. Here comes the modifications. Once I have imported the Application table, I need to import the at-identity schema, and I also need to make the Application record a Complex Type for later use. Because the prefix for the namespace is tns, the Data Structure Type needs to be tns:Application
I then add the primary key field and set the Data Type to the at-identity
After saving it, I then go back to my Header schema and import both the at-identity and the application schema
I then add a new record as a sibling to Header, and I name the new record Application (the name of the table), I choose as the Data Structure Type ns0:Application (ComplexType) and set the Max Occurs as blank, as we will not be repeating until later
I complete the process for all of the other tables, except for the tables that do not have children, and there I don't need to select the primary key field (as I will let SQLXML do that without me coding for that)
So I generate the schemas for everything and now we are getting to childless table importing, ProviderInfo for example, I don't need to add the primary key, I simply need to include the foreign key when I generate the schema
Remember that all I have to do is define the Data Structure Type as tns:ProviderInfo for the table name
When I import the InsertProviderInfoService into the InsertHeaderService schema I choose ns2:ProviderInfo and again, since it is not repeating, you can simply leave the Max and Min Occurs blank, for the repeating, you will change the max occurs to Unbounded
You continue this same pattern, adding primary keys if there are children to the table, omitting them if not.
The final output should look like this:
Delete all of the Orchestrations except the first one that has defined the Header and rename it to InsertClaimProcess and change the Typename to InsertClaimService also
Now for the mapping (finally). Create the map with the EDI input, and the header schema output. Since the Header, Application, and provider are all single instances, I hard code a, b, and c respectively into the updg:at-identity primary key and the foreign keys. For the ProviderInfo, ProviderLocation, and Subscriber, I link them all to 'c'
Since Subscriber is the first repeatable child table, I add an index from the source to the d primary key and then distribute the concatenated foreign key thru the child tables
Finishing out the relationships look like this:
Now all you have to do is finish the mapping and I am complete, invoke the map in the orchestration and it is complete.
Hope that this was as fun to do as it was to discover!
posted @ Friday, April 13, 2007 12:00 PM