Help – Entering multiple child records using sql adapter

Home Page Forums BizTalk 2004 – BizTalk 2010 Help – Entering multiple child records using sql adapter

Viewing 1 reply thread
  • Author
    • #15102

      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.


      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…


      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

      • #15103


        I have two tables Table1 and Table2 in SQL 2000 db instance. I need to enter following data(parent-child) in these two tables.

        — sample file start——

        <?xml version=\”1.0\” encoding=\”utf-8\” ?>
        – <ns0:SampInst xmlns:ns0=\”http://TestFFSchemaProj1.SampInstSchema1\”>
        – <OutputHeader>
        <StrVariable1 />
        – <OutputValues>
        <var1>107.0 1</var1>
        – <OutputValues>
        <Lref>107.0 1</Lref>
        – <OutputValues>
        <Lref>100.0 1</Lref>
        – <ReferenceNumber>

        — sample file end——

        I am able to enter the data specified whithin <outputheader> into Table1 successfully. But I am only able to enter one child record from from the element <OutputValues> whithin Table2. I need to enter all rest of the child records whithin Table2.

        Need help..

        • #15104

          Thanks for your meaningful feedback.. I also looked at the example available at,

          [url] [/url]

          and then I updated the schema and the mapper in my project to solve my issue.

          Great help from you and from that example.


    • #15101

      See also my blog: How to update many records from Biztalk using a Stored Proc.

Viewing 1 reply thread
  • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.