Help – Entering multiple child records using sql adapter

promote the foreign key value in your schema for table 2. you will then need to loop each time for that message and insert the foreign key so that SQL knows who the parent is.

now, as i am not 100% sure of your situation let me give you a broad heads up on what i am saying.

each sql adapter schema is bound to one table (typically) so in other words, think of it as an insert statement in sql. Insert into foo (boo,bla) values (a,b) scope_identity;
you would then take the resulting primary key and insert that into the children tables as many times as they occur
insert into foochild (blah,blah,foo_fk) values (a,b,oldkey);

you are either not looping on the children (hence only the top child gets inserted) or you are feeding this schema in to insert to two tables (sort of like appending the insert statements) and hence will only insert the parent and first child.

solution:

create a map that breaks out the children. basically loop on that child structure (<OutputValues>) and creates a whole new message for that data. of course, add the foreign key and feed it to the sql adapter. That will get all the children into the db….

translating that into C# methodology :

nodelist A = xdoc.selectnodes(‘\\\\outputvalues);

foreach(node x in A)
//extract node data, create sql statement and shoot to your db…

return;

now this can be put in an expression shape straight away, or into an external C# dll that is called from an expression (my preference for the power of a C# class file compared to an expression) or doing it using biztalk orchestration shapes (also a great solution, but sql adapters in biztalk are still weak in my opinion).

hope this helps and feel free to ask me again if it doesnt