Pages

Tuesday, September 30, 2008

Generate Edifact message from a xml

The people of DataDirect made a great product called XML convertors with this you can generate an Edifact message from a xml and back. This is not new but this java software can easily embedded in your own java program. In this blog entry I will show how you can generate an edifact message in a few hours and very flexible.

This are the steps to generate an APERAK Edifact message from an Oracle table.
Step 1 generate the xsd from xml convertor for our edifact message.
In this example I want to generate an Aperak Edifact message so I first need a xml schema,then I know how the xml must look like. You have to do this only once.

ConverterFactory factory = new ConverterFactory();
try {
Result xsdResult = new StreamResult("d:/projecten/xml_edi/aperak.xsd");
String uri = "EDI:dialect=EDIFACT:version=D96A:message=APERAK:tbl=no";
SchemaGenerator generator = factory.newSchemaGenerator(uri);
generator.getSchema(xsdResult);
System.out.println("schema generator finished: --> aperak.xsd");
}
catch (Exception e)
{
System.out.println("schema generator failed with exception: " + e);
}

This how the xsd looks like.

Step 2 generate a xml from the oracle database with xmldb
With Oracle xmldb we can generate the following xml from the database.

<message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="db.xsd"
type="APERAK" date="080928" time="1347" runId="1001" test="true">
<records>
<aperak_rff nr="1" message_id="A_200508030000603782" ref_qlfier="AAN" ref_nr="T200508031110N002" message_function="27"/>
<aperak_nad nr="1" message_id="A_200508030000603782" code_ontv_afz="A" party_qlfier="GRP" ean_code_party="8716867999990"/>
<aperak_nad nr="2" message_id="A_200508030000603782" code_ontv_afz="O" party_qlfier="GRU" ean_code_party="8712423009097"/>
<aperak_dtm nr="1" message_id="A_200508030000603782" dt_qualifier="137" dt_period="200508031112" dt_format_qlfier="203"/>
<aperak_dtm nr="2" message_id="A_200508030000603782" dt_qualifier="178" dt_period="200508031113" dt_format_qlfier="203"/>
<aperak_dtm nr="3" message_id="A_200508030000603782" dt_qualifier="735" dt_period="2" dt_format_qlfier="805"/>
<aperak_erc nr="1" message_id="A_200508030000603782" error_code="00023" nr_text="1" volgnummer="505484510" error_text=" (E) "/>
</records>
</message>

You probably noticed I do not have all the required elements in this database xml to generate a complete edifact message. But we will solve this in the next step.
With Altova XMLSPY I will generate a new XSD based on this example xml.

Now we have two xsd's and a database xml, we can use Altova Mapforce to create a xslt which can transform the database xml to the more complex edifact aperak xml.
Step 3 Use Mapforce to create a XSLT.
I will use Altova Mapforce because with this program I can visual make a xslt and immediately see the result of the xlst. We need to import the two xsd's in mapforce and make the mappings between these two xsd's and add the missing data to the edifact aperak schema.

This is the output of the xslt.

<?xml version="1.0" encoding="UTF-8"?>
<EDIFACT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="D:/projecten/xml_edi/aperak.xsd">
<UNB>
<UNB01>
<UNB0101>UNOC</UNB0101>
<UNB0102>3</UNB0102>
</UNB01>
<UNB02>
<UNB0201>8712423009097</UNB0201>
<UNB0202>14</UNB0202>
</UNB02>
<UNB03>
<UNB0301>8716867999990</UNB0301>
<UNB0302>14</UNB0302>
</UNB03>
<UNB04>
<UNB0401>080928</UNB0401>
<UNB0402>1347</UNB0402>
</UNB04>
<UNB05>1001</UNB05>
<UNB11>1</UNB11>
</UNB>
<APERAK>
<UNH>
<UNH01>1001</UNH01>
<UNH02>
<UNH0201>APERAK</UNH0201>
<UNH0202>D</UNH0202>
<UNH0203>96A</UNH0203>
<UNH0204>ZZ</UNH0204>
<UNH0205>EDINE1</UNH0205>
</UNH02>
</UNH>
<BGM>
<BGM01>
<BGM0101>12E</BGM0101>
<BGM0103>9</BGM0103>
</BGM01>
<BGM02>A_200508030000603782</BGM02>
<BGM03>27</BGM03>
</BGM>
<DTM>
<DTM01>
<DTM0101>137</DTM0101>
<DTM0102>200508031112</DTM0102>
<DTM0103>203</DTM0103>
</DTM01>
</DTM>
<DTM>
<DTM01>
<DTM0101>178</DTM0101>
<DTM0102>200508031113</DTM0102>
<DTM0103>203</DTM0103>
</DTM01>
</DTM>
<DTM>
<DTM01>
<DTM0101>735</DTM0101>
<DTM0102>2</DTM0102>
<DTM0103>805</DTM0103>
</DTM01>
</DTM>
<GROUP_1>
<RFF>
<RFF01>
<RFF0101>AAN</RFF0101>
<RFF0102>T200508031110N002</RFF0102>
</RFF01>
</RFF>
</GROUP_1>
<GROUP_2>
<NAD>
<NAD01>GRP</NAD01>
<NAD02>
<NAD0201>8716867999990</NAD0201>
<NAD0203>9</NAD0203>
</NAD02>
</NAD>
<NAD>
<NAD01>GRU</NAD01>
<NAD02>
<NAD0201>8712423009097</NAD0201>
<NAD0203>9</NAD0203>
</NAD02>
</NAD>
</GROUP_2>
<GROUP_3>
<ERC>
<ERC01>
<ERC0101>00023</ERC0101>
<ERC0103>60</ERC0103>
</ERC01>
</ERC>
<FTX>
<FTX01>AAO</FTX01>
<FTX04>
<FTX0401> (E) </FTX0401>
</FTX04>
</FTX>
</GROUP_3>
<UNT>
<UNT02>1001</UNT02>
</UNT>
</APERAK>
<UNZ>
<UNZ01>1</UNZ01>
<UNZ02>1001</UNZ02>
</UNZ>
</EDIFACT>

Now we can generate the database xml and have an xlst to transform the xml to the edifact aparak xml. In the last step we can make the edifact message with the xml convertor software.
Step 4 Generate the edifact message
What really cool is that xml convertor can detect the xml message and transforms it to the right edifact message so you only need this code for all your outgoing edifact messages.

try {
Source converterSource = new StreamSource("d:/projecten/xml_edi/aperak_gen.xml");
Result converterResult = new StreamResult("d:/projecten/xml_edi/aperak.edn3");
Converter fromXml = factory.newConvertFromXML("converter:EDI");
fromXml.convert(converterSource, converterResult);
System.out.println("fromXML finished: aperak.xml -> aperak.edn2");
}
catch (Exception e)
{
System.out.println("fromXML failed with exception: " + e);
}

This is the generated edifact message
UNA:+,? '
UNB+UNOC:3+8712423009097:14+8716867999990:14+080928:1347+1001++++++1'
UNH+1001+APERAK:D:96A:ZZ:EDINE1'
BGM+12E::9+A_200508030000603782+27'
DTM+137:200508031112:203'
DTM+178:200508031113:203'
DTM+735:2:805'
RFF+AAN:T200508031110N002'
NAD+GRP+8716867999990::9'
NAD+GRU+8712423009097::9'
ERC+00023::60'
FTX+AAO+++ (E)'
UNT+11+1001'
UNZ+1+1001'

Conclusion, this is an flexible solution to generate an Edifact message. In your java application you can easily embed the xml convertor software and use the xslt to transform the xml. With this solution you can change your backoffice application or edifact message. You only have to change the xslt which is very easy in Altova Mapforce.

7 comments:

  1. Interesting article! I just wanted to also point out that you can convert your DB data directly into EDIFACT in Altova MapForce Enterprise Edition, which has full support for EDI (EDIFACT and X12) and supports a wide variety of DBs, including Oracle. You can also generate Java (or C#, C++) code and embed it in your applications.

    ReplyDelete
  2. Hi Liz,

    I know mapforce ( great product ) but with mapforce you have connection to the Oracle database ( it is not loosely coupled, we use an ESB to deviler the xml's ). The second reason why I like xml convertor, mapforce generates java code for every message, this code has to be included in your own application.
    xml convertors can detect which conversion it has to do.

    ReplyDelete
  3. thank u r information

    it very useful

    u r blog Is very nice

    ReplyDelete
  4. Nice Article!
    You could also use DataDirect's XQuery product in combination with the XML Converters to query your relational database and stream the result directly into EDI (for more info see www.xquery.com).
    Those who are not familiar with XQuery could use the Stylus Studio mapper to write your XQueries. BTW, Stylus Studio Enterprise has the DataDirect XQuery engine and XML Converters technology integrated!

    ReplyDelete
  5. I bought Stylus Studio Enterprise Edition, because the claim that it comes with EDI converters. But they don't say that you have to pay thousands of dollars to use them on a server. They don't have the price for that on their website, you need to call their sales guys!. Just do your research before buying. I am going to take a look at Altova.

    ReplyDelete
  6. Hi Edwin,

    Execellent Article, I just want one help edwin, right now i m working based on this project only, if u dont mind can u just post me the sample project in java

    ReplyDelete
    Replies
    1. Hi,

      with Map Force you can generate your own java code which can do this. just have your own input xsd and choose your edifact defintion as output. Add the mappings and generate the code.

      thanks

      Delete