Biztalk EDI to XML or EDI to Updategram conversion and table design for purchase order RRS feed

  • Question

  • hi all

    i have an EDI document with me and i am also mapping it to one of schema call OrderFile.xsd but i have few question


    Multiple vendors will be registered with required information. (they will provide information in EdiFact - ver.D96A only ).
    I want to store this information coming from various vendors into SQL Server Database so that whenever any smallest information is required it can be fetched from the database. i.e. something like data mining.
    e.g. All the information about products belogs to specific product category & avalibale in Asian region.

    Which is the best way to store this EdiFact file?
    1) Directly storing EdiFact file into SQL Server (I don't think it's good idea for data mining)
    2) Convering EdiFact file into XML format & storing XML file into SQL Server as XML data type. (I don't know how good or easy to query using XQuery & XPath for data mining)
    3) Converting EdiFact file into XML & creating Dataset from this XML & storing the Dataset into SQL Server in custom defined data structure.
    ( Here question about database design)

    It would be better if anyone can share views on it. If any sample application or database schema for option3 would be better.

    so i am really confused as i am not getting how i would be designing the DataBase for it




    Monday, April 12, 2010 1:32 PM


  •  You can do this in couple of ways

     1. You can try using BAM for tracking metrics- When you receive the message as EDIFACT or OrderXml.

     2. You can also create a Canonical schema and create a custom table for tracking information b'coz in your case it looks like you need order details. So when you map from EDIFACT to Order.xsd do whatever you want with the message in addition you can have one more subscription to push the data to the database. You can store as Xml or data as separate fileds. If your not comfortable with XQuery I would say go and store them as separate fields and retrieve as needed. But make sure you archive your custom database on periodic manner.





    Monday, April 12, 2010 3:14 PM