SQL Server: EDI to XML Data Conversion

Question

I need to do the data conversion from EDI format to XML. is there any step by step tutorial, links about what are the processes on data conversion? How to convert from EDI to XML, step by step guide?

I highly appreciate your help.

Thanks

Haffner

Answer 1

That's a very complicated question.

First, the short answer: Use a commercial product (an EDI translator/mapper).

For a longer answer, there are several different EDI standards:

• UN/EDIFACT
• ANSI ASC X12
• Several others (see the EDI link above).

There's also the question of whether the EDI is in binary or XML format. Since you're trying to convert it to XML, I'll assume the former. (If it's already in XML format, schemas are available for X12 at least.)

Parsing binary EDI is non-trivial. Dealing with other interchange requirements (such as Functional Acknowledgments), combined with the necessity of having a three-inch-thick standards book on your desk to look up all the segments and elements for one version of one standard implies favoring "buy" over "build."

EDI translators/mappers are not cheap, and there's a learning curve. When your trading partner changes versions or standards, adds documents and segments, and/or requests new documents in return, the cost pays for itself.

I'm not up to date on the current crop of software; search on "EDI translator" for some of them. I believe BizTalk has some support for this as well. Perhaps other posters can recommend one.

Benedictbenedicta

Jitterbit is sort-of like BizTalk and has an open source version. Unfortunately, the EDI plug-in for it requires the paid version due to licensing. – Lieutenant

Answer 2

Like TrueWill said, it's a complicated question. Perhaps you can provide a little more detail about what you need to do. If you have a one-time conversion with just a few documents, you might be able to code your own converter. If you're in for more than that, I have to agree that you'll want to buy something. What you buy depends on your needs.

Are you processing these things real-time? There are some products that have runtime converters you can expose to the web (Mercator, StylusStuido, BizTalk, Webmethods? Not sure about that one)).

If you're dealing with EDI from multiple sources, you'll have to deal with different line lenghts, encodings, separators, versions of the standard, ways they use the standard document, acknowledgement requirements, and mistakes that the people sending you data will make that they claim they're not making.

Some of the best tutorials I've found are done by Stylus Stuido. They're specific to their product, but they do a good job of explaining things that you can apply to other products if you're using them. http://www.stylusstudio.com/learn_xml.html. Their forums are pretty good too.

Creech