Apr 132007
 

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.

image

and

image

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!

  • dev

    Hi,

    I have question on your post “Inserting into multiple tables with multiple Pk/Fk relationships”

    I have very similar requirement coming up. The tables are around 100 with parent and child relationship and need to import 837 X12 data into those 100 tables for each claim.

    We get 40K-50K claims every day.

    The existing database is in Oracle.

    Do you think if i follow your solution(as in the post) to implement Biztalk to load into those 100 table in oracle database, will it be scalable enough to load large volume?

    otherwise this can be achieved better in SQL server?

    Any inputs will be helpful!

    Cheers!
    Dev

  • SQL will be much faster, but then you are doing the same amt of work to load it to SQL to then go to Oracle.
    I have this set up for an 837 and it works fine. XSLT is not terribly fast, but there isn’t a problem in this setup regardless of the complexity.

  • Chris Miller

    Excellent article – thank you!

    Does the 10,000 local variable limitation mean that you can’t use a schema with 10,000 element/attributes – or does it mean that you can’t populate more than 10,000 rows * columns from a single XML message? I am not clear how local variables are used under the covers…

  • It means that there can only be 10,000 relationships created, you can have a small schema, but the A1 that is created at the parent, and the subequent A1 values sent to children, if there are more than 10,000 of those, you hit the hard coded limitation in SQL Server. I have created a BulkXML adapter that does not have this limitation (plus it is a h#** of a lot faster than row by row inserts like the SQL Adapter does).

  • Rajashekar

    I have scenario where I need to insert into child table only for special type of header record.
    When I tried the solution provided It didn’t work, since child record is blank it throws exception.

    HRESULT=”0x80070057″ Description=”Invalid Argument”
    <Root xmlns:ns00=”urn:schemas-microsoft-com:xml-updategram”><?MSSQLError HResult=”0x80070057″ Source=”Microsoft OLE DB Provider for SQL Server” Description=”Invalid Argument”?></Root>

    Do we have a solution for this kind using UpdateGram or we need use Stored Procedure ?

    Thanks,
    Rajashekar

  • You need to put a boolean that drives the creation of the table so if there is no data, there is no record created.

  • Rajashekar

    Thanks, could you be more specific on this.
    I but the conditon in MAP, if I have to populate the child table then I check for that node and then map the value to the destination schema using Value Mapping functoid. But still no success.

  • Ian Jolly

    Hi Eric,
    Thanks for article. I`m trying to achieve a similar goal but the difference is that for each table insert/update needs to call a individual stored procedure containing specific logic. Is this possible? In other words can I make calls to multiple SP’s within the same request?

    Kind Regards

    Ian

  • I have scenario where I need to insert into child table only for special type of header record.

  • This is such a great resource that you are providing and you give it away for free. I love seeing websites that understand the value of providing a quality resource for free. It is the old what goes around comes around routine. Did you acquired lots of links and I see lots of track backs??

  • This looks absolutely perfect. All these tinny details are made with lot of background knowledge. I like it a lot. Keep on taking action!

  • Th4t be an epic da shizzi4 post, th4nkie 4it & in da futures we’ll be seeing more of it

  • I was looking for information about multiple tables.

  • Thanks for the informative tips shared here! Got only positive emotions and dozens of ideas that could be useful for my blog. Thanks it was just another motivation to devote some more of time and effort to my start up! Regards

  • Very useful, me too like others programmer sometimes need some help some these stuff or problems.
    Thanks for you article.

  • Thank you for the extremely impressive article. It has great detail that are easy to understand and it also has great tips. I can’t wait to read more of your blogs.

  • Great explanation of this database structure. I also use such pictures for explaining my database constructions. 🙂

  • I like thist article very much ,Thank you for sharing! When I cliked on the title-a title I might add,which is quite good. This post particularly caught my attention. hanks for sharing look forward your new posts 8

  • Filipe Gaspar

    Hello Eric
    I create a biztalk project (VS2005) according the instructions in your blog, but the fk of the child tables are all being updated with the identity of last pk (father table) inserted.
    Do you have any idea of what is making this happen? I’m using SQL Server 2005.
    Thanks

    King regards

  • t understand the value of providing a quality resource for free. It is the old what goes around comes around routine. Did you acquired lots of links and I see lots of track bac

    Read more: http://blog.biztalk-info.com/archive/2007/07/29/Inserting_into_multiple_tables_with_multiple_PkFk_relationships.aspx#ixzz1MmC4gZEx

  • of links and I see l

  • links and I see lots of tra

  • tinnitus treatment

    It is a great articles. it gives me a lot of information. before reading this article, I do not know about it. but now, after reading your article, I get it. Thank you..

    tinnitus treatment

  • This is a great post ! it was very informative. I look forward in reading more of your work. Also, I made sure to bookmark your website so I can come back later. I enjoyed every moment of reading it.
    make money online

  • local seo services

    It is a great articles. it gives me a lot of information. before reading this article, I do not know about it. but now, after reading your article, I get it. Thank you..

    local seo services