none
Send EDI loop data to SQL Stored procedure RRS feed

  • Question

  • Hello,  BizTalk Newbie here. I'm trying to send EDI 830 N1 data to a stored procedure as parameters (e.g. ManufacturerName, ShipToName, ShipFromName, IssuerName).

    I want to combine the four lines in the EDI document into four parameters to pass to the Stored Procedure creating just one record in the database.

    When I use an  Equals Function with a Value from the  N101 segment I can send a single selected line to the Stored procedure.  Otherwise I get four records in the DB as the Mapper loops through N1.

    I see that that some people are using XSLT for this scenario.  I'd prefer to use the mapper to loop through N1 and assign the values from the four N1 nodes to four variables and pass these to SQL.

    Does anyone have any ideas, or at least some better documentation on how to use XSLT with BizTalk?

    Thanks in advance.
    Monday, February 23, 2009 9:35 PM

Answers

All replies

  • Hi,

    I agree, there's not much greaqt documentation on XSLT in BizTalk, and it's a very useful technique.

    I've done a 20 minute webcast on using Custom XSLT in BizTak Server:

    http://bloggersguides.net/media/p/22.aspx

    I alos look at custom XSLT with EDI docuemts, I'm using EDIFAC, but it should be similar to X12.

    Regards,

    Alan

    BloggersGuides.net
    Tuesday, February 24, 2009 8:46 AM
  • This will be covered in detail in our book Pro Mapping in BizTalk Server 2009 which will be on the shelves March 23.

    You should have four target blocks on the output side, one for each of the following:  ManufacturerName, ShipToName, ShipFromName, IssuerName.  Correct?  You should be able to populate all four using Value Mapping (Flattening) functoids in combination with Equals functoids, one for each block.

    When you say "four lines" and "four parameters", are you talking about four items or four groups of data?

    The answer to your question depends on the specific layout of your target schema -- can you show us that?



    Jim -- Pro Mapping in BizTalk 2009, Apress Books, March 23, 2009
    Tuesday, February 24, 2009 3:55 PM
  • Thanks Jim. I'm eagerly awaiting your book.  Microsoft's Mapping information is..... lacking......

    I did set up a test with Value Mapping (flattening) functoids, a link from N1Loop to Target Schema root node and some equals functoids to grab the N101 values and it worked well.  Alan's method also works well, but I may have need to use the mapper so Business Analysts can review maps. 

    The target schema is "Flat" for lack of a better word. I'm just passing several params. to a stored procedure that in turn populates a SQL table. 

    The source schema (EDI 830 3020) has an N1 loop like such:

    N1~MI~COMPANYISSUING~1~999999999*
    N1~ST~COMPANYSHIPTO~92~3333*
    N1~SU~COMPANYMANUFACTURING~92~111111*
    N1~SF~COMPANYSHIPFROM~1~8888888*

    I use the equal functoed on the N101 value (e.g.  ST for Ship To) to map the value COMPANYSHIPTO to SQL @NameOfShipTo.

    This data gets mapped to the stored procedure params:
    @NameOfIssuer nchar(50)='', @NameOfShipFrom nchar(50)='', @NameOfShipTo nchar(50)='', @NameOfManufacturer nchar(50)=''

    This all seems rather messy, but for this case (830 Release header) is working well.

    Between XSLT and mapping there is a lot to learn here.



    Tuesday, March 3, 2009 9:36 PM