none
SQL Adapter produce non XML message (EDIreceive pipeline) RRS feed

  • Question

  • I've created a new SQL adapter that runs a stored procedure that returns a single column that contains an X12 EDI message.
    This message is bound for an EDIReceive pipeline. However, the message returned is wrapped within XML tags.

    Is there some method, or option, to force the SQL Adapter to not wrap the data within any XML tags?
    I just want the data for standard EDI processing

    Wednesday, September 16, 2009 12:23 PM

Answers

  • If you are using the WCF-SQL adapter, you can try the following-

    During the configuration of your  Receive Location using WCF-Custom, navigate to the Messages tab. Under the section "Inbound BizTalk Messge Body", select the "Path" radio button, and:
     
    Enter the body path expression as:
    /*[local-name()='element1']/*[local-name()='element2'].....

    i.e  the Xpath of the the  part of the message you are intrested in.

    Wednesday, September 16, 2009 12:48 PM

All replies

  • If you are using the WCF-SQL adapter, you can try the following-

    During the configuration of your  Receive Location using WCF-Custom, navigate to the Messages tab. Under the section "Inbound BizTalk Messge Body", select the "Path" radio button, and:
     
    Enter the body path expression as:
    /*[local-name()='element1']/*[local-name()='element2'].....

    i.e  the Xpath of the the  part of the message you are intrested in.

    Wednesday, September 16, 2009 12:48 PM
  • Your question is a little confusing. Are you referring to the WCF-SQL adapter or are you using a For Xml syntax in the stored procedure to generate the Xml?

    If you are using the WCF-SQL adapter, take this approach:

         I wrote a blog entry on this a while ago: http://msinnovations.spaces.live.com/blog/cns!62E68922E47BC425!470.entry

         I found that there is usually some additional wrapping by default with a part element. My suggestion is similar to Rohit but you need to also be aware of this wrapping element. This is the default behavior of wrapping with a SOAP message contract as long as you have not specified any custom behaviors to override this or a message contract for a custom WCF service. Here is a link on this: http://msdn.microsoft.com/en-us/library/system.servicemodel.messagecontractattribute.iswrapped.aspx.

    Otherwise,
      I would ask if you are using the Xml so that BizTalk consumes it as a message? You might use a custom pipeline component to pull out the EDI body right before it hits the EDI Receive.

    Thanks,
    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Thursday, September 17, 2009 5:34 AM
    Moderator
  • Ben,
    Sorrfy for the confusion. I was originally working with the SQL adapter as delivered in 2006 R2. But based on Rohit's response I spent the day installing the adapter Pack SP2 to look at his approach. As a side note, this has turned into a bit of a separate process in that I have not been successfull in simply getting the adapter pack installed correctly (but that is a different issue).

    My Stored Procedure, for this example, was a simple version on which I can build a production ready solution. In this case it was
    "Select [raw] from tbl_Import".
    I let this message go into suspense and found the XML encoding and namespace tags wrapped around my data. since my original intent was to send this message into the EDIReceive pipeline as we do with all other raw edi sources. The feeling was to let things remain simple, thus, the operations team could handle the system better.

    Because of time constraints I may work on the Adapter Pack install for a day or two more. I will create a new forum thread for that issue. If I do panic (only 3 weeks left to finish this projeect), I will probably follow your approach about a custom pipeline. I'm sure I cannot get up to sufficient speed on WCF in such a short time.

    I'm off to review your references at this time. But thought I should respond, at least, to you first comments.

    Thanks
    Thursday, September 17, 2009 11:58 AM
  • Yeah, sometimes it takes a while to get the adapter pack to install properly, esp. for certain adapter prerequisites and on 64-bit.

    The WCF-SQL adapter does provide a lot out of the box and is a much better VS experience than the older SQL adapter.

    Another approach would be to use a stored procedure to just return the EDI as a string and write this out to a folder for the EDIReceive to pick up.

    Thanks,
    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Thursday, September 17, 2009 5:09 PM
    Moderator
  • Thanks, I finally got the adapter to work.
    Heh, Why does it always come down to reading instructions properly? It took me about 30 minutes to distinguish 'PolledDataAvailableStatement' from 'PollingStatement'. To quote some movie line for my past: "Ha ha, it is to laugh".

    I'm seeing data finally coming in. and now onto setting up the XPath statement.  I can already see that this will be immediately followed by dealing with large messages. I'm seeing the document being split acroess multiple <NewTable> Tags. It looks like a buffer issue. But one step at a time.

    In regards to using a temporary folder, I do believe that would be an easier approach and I am saving that in case I have to punt. My concern is that in dealing with network drives, there will be additional risk, at least here. If there was ever an issue, it would be back on my desk since operators and admins would not have the time to try figure the process out. With this approach, There are SQL Server Admins that are on watch to keep SQL Server running (and Biztalk), if there is ever a disconnect, the system has a better chance of self healing.


    <side note> My original idea was to build a custom BizTalk Adapter (either straight or using WCF), however, I just can't get up to speed in time to meet our deadline. I've already written a simple windows Service that polls a Web Service and recurses through the nested Zip files to extract all the EDI data files. Each one is written to SQL Server to be picked up by the SQL Adapter for regular EDI processing.
    </side note>


    I will update as I go along. Thanks for your time and thoughts
    Thursday, September 17, 2009 5:57 PM
  • Final update -
    Once I got the XPATH stmt I'm getting a clean extract and the message is running successfully into the EDIReceive Pipeline. Thanks Rohit and Ben for your assistance. Now just some code clean-up and we'll get this bad boy into production
    Friday, September 18, 2009 7:07 PM
  • How did you get around the issue of the data being split across the multiple <NewTable> elements?

    Wednesday, February 17, 2010 9:06 AM
  • Unfortunately Sibcool, I did not have that issue. I was retrieving a single row and column. So my XPATH statement returns the single chunk of text I require. In my case the retrieved block was an EDI message as a string. while I do recall that your question has arisen in other forum discussions, I cannot recall of the top of my head. I will take a look around later this morning and hopefully I can find something helpful.
    If not, you may want to open a new request in the group. I've found the people here an excellent resource and surprisingly timely.


    Wednesday, February 17, 2010 1:08 PM