none
Configuring ST segment in an EDI party to get each record as one service event. RRS feed

  • Question

  • Hi,
          I am able to generate the EDI but all the records from a table are coming under one ST and one SE segment. I am trying to generate each record as one ST and SE segment. Any inputs on this.

    Thanks in Advance.
    Shashi
    Friday, May 1, 2009 7:14 PM

Answers

  • The problem with Ajeet's response is that  for every Response you are getting a transaction.
    What you are getting is this:
    
    
    <SQLResponse>
      <Row attribute1="" .../>
      <Row attribute1="" .../>
    <SQLResponse>
    


    You really need this:
    <SQLResponse>
      <Row attribute1=""... />
    <SQLResponse>


    And then another message 
    <SQLResponse>
      <Row attribute1=""... />
    <SQLResponse>
    

    There are a couple of different approaches to accomplishing this:
    You can have an envelope schema defined that will break up the rows that are returned from the stored procedure into individual messages so when the results show up in the message box, you have one message per row. You can learn about how to do it starting here: http://msdn.microsoft.com/en-us/library/ms944682.aspx 

    The second is that you can split the resulting message in an orchestration, where you create individual messages from your sql result. A good example of how to do it is written here: http://codebetter.com/blogs/jeff.lynch/pages/128664.aspx

    Eric Stott [http://blog.biztalk-info.com] - Mark as Answer if this reply does.
    Tuesday, May 5, 2009 5:49 PM

All replies

  • Hi,
        I did not get your problem completly . can you explain more about table and records? From which format to edi you are generating edi?

    Thanks
    Gyan

    If this answers your question, please mark it as "Answered".
    Friday, May 1, 2009 9:40 PM
  • Hi,
            I am generating EDI format from SQL format. While trying this the ST segment is not getting looped for each record. Instead iam getting all the records in one ST segment.

    THanks,
    Shashi
    Monday, May 4, 2009 5:43 AM
  • Hi,
        Can you explain more about records and table. and any sample that it looks like?


    Thanks
    GYan
    If this answers your question, please mark it as "Answered".
    Monday, May 4, 2009 5:47 AM
  • As I understand, you are using SQL adapter(Stored Procedure) to receive some record from database table and mapping that record to EDI message. Doing so all the records are getting mapped into the same EDI message.

    I think you need to disassemble the SQL Message(each record) at pipeline level then mapping each record separately.
    For Eg:

    You SQL response must have structure like

    <SQLResponse>
    <Record>
    </Record>
    <Record>
    </Record>
    <Record>
    </Record>
    </SQLResponse>

    In this case you have to separate out records at pipeline level.To create custom pipeline follow these steps:
    1)Make sure MaxOccur for <Record> is 1.
    2)Drag a XMLDisassembler and select the SQL Response schema as document schema

    Hope this will resolve the problem.Let us know if still you face any issue.
    Ajeet Kumar
    Monday, May 4, 2009 1:00 PM
  • Hi Ajeet,
                  Thanks you very much for the infromation. Yes exactly that is the structure what i am trying to do. Here for the SQL response we have attributes which do not have the max and min occurances.

    I have tried to configure Max Occurs = 1 for the root node and created custom pipeline with XML disassembler but it seems not to work.

    Thanks,
    Shashi
    Monday, May 4, 2009 11:48 PM
  • You need to split up your SQL results to one row to create 1 transaction (837, 820, 834, etc), this will create one ST-SE for each result.
    Eric Stott [http://blog.biztalk-info.com] - Mark as Answer if this reply does.
    Tuesday, May 5, 2009 1:38 AM
  • You mean you have structure like
    <SQLResponse>
    Record1      Attribute
    Record2      Attribute
    Record3      Attribute
    Record4      Attribute
    </SQLResponse>
    <SQLResponse>
    Record1      Attribute
    Record2      Attribute
    Record3      Attribute
    Record4      Attribute
    </SQLResponse>

    If it is like this then you should create one Child Record which will hold the attributes like
    <SQLResponse>
    <Records>
    Record1      Attribute
    Record2      Attribute
    Record3      Attribute
    Record4      Attribute
    </Records>
    <Records>
    Record1      Attribute
    Record2      Attribute
    Record3      Attribute
    Record4      Attribute
    </Records>
    </SQLResponse>

    I think you must have created the schema manually, if you create schema using Add Generated Items(using Stored Procedure) then it will create the schema with proper structure.

    Then make sure the MaxOccur of <Records> is 1 and use the same schema in the custom receive pipeline.

    If issue still persist and if possible then you can send me your schema or Stored Procedure to verify the real cause. Can mail me at ajk86in@gmail.com


    Ajeet Kumar
    Tuesday, May 5, 2009 5:30 PM
  • The problem with Ajeet's response is that  for every Response you are getting a transaction.
    What you are getting is this:
    
    
    <SQLResponse>
      <Row attribute1="" .../>
      <Row attribute1="" .../>
    <SQLResponse>
    


    You really need this:
    <SQLResponse>
      <Row attribute1=""... />
    <SQLResponse>


    And then another message 
    <SQLResponse>
      <Row attribute1=""... />
    <SQLResponse>
    

    There are a couple of different approaches to accomplishing this:
    You can have an envelope schema defined that will break up the rows that are returned from the stored procedure into individual messages so when the results show up in the message box, you have one message per row. You can learn about how to do it starting here: http://msdn.microsoft.com/en-us/library/ms944682.aspx 

    The second is that you can split the resulting message in an orchestration, where you create individual messages from your sql result. A good example of how to do it is written here: http://codebetter.com/blogs/jeff.lynch/pages/128664.aspx

    Eric Stott [http://blog.biztalk-info.com] - Mark as Answer if this reply does.
    Tuesday, May 5, 2009 5:49 PM
  • Thank Eric. That is exactly what i need to do. I will try the two approches and let you know.

    Shashi
    Wednesday, May 6, 2009 10:23 PM