|
|
 next
|
  Subject: Automated EDI to/from SQL - issues so far
Author: Steve R
Date: 22 Feb 2006 09:52 AM
Originally Posted: 21 Feb 2006 03:40 PM
|
|
First I'll start by stating I may be trying to use the product for more difficult tasks than it was designed. I'm trying to avoid using the $15-$20K EDI translators (Gentran, Biztalk, etc). At first glance, it appeared Stylus Studio could potentially meet our requirements, which are:
1. Receive multiple ANSI X12 EDI files from ftp directory on an automated schedule
2. Translate each EDI file into multiple Microsoft SQL server tables w/ parent child relationships
3. Generate EDI file from multiple SQL tables on demand from web app
4. Notification of errors when they occur<br>
My issues so far with the above requirements are:
1. Is the best approach to get the EDI files from the ftp first in my own app then call Stylus with each file as a parameter to translate them?
2. Can all of the above steps be automated? Or are some of the functions required not in the API yet?
3. The Stylus X12 schemas limit the number of ISA's to one per file. This doesn't follow the standard. We receive multiple ISA's in each file.
4. Updating the database - I've read "Working with Relational Data as XML" (http://www.stylusstudio.com/docs/v62/d_rdb7.html) several times. I'm not clear on exactly how to update the SQL server tables programmatically, or if it is even possible. From the steps involved, it appears that creating a MS SQLXML updategram would be easier. Am I missing something?
5. In the documentation, it says you can use a .rdbxml file anywhere you would use an xml file. I assume to map to the database, my XLST would map from my EDI file to the .rdbxml, however, an .rdbxml is not accepted as a source. How should I map this?
6. If the map fails for any reason, will I be able to know this programmatically? Will the entire transaction fail or will part of the record be written to the db?
If Stylus Studio is not yet ready or was not designed to handle this functionality, I understand. Although if it was, you sure could win over a lot of the traditional EDI translator users.
Thanks,
Steve
---
<?xml version='1.0' ?>
<xsl:template match="/">
<root>
<xsl:for-each select="X12/TS_830/GROUP_3/GROUP_7">
<row>
<PartID>
<xsl:value-of select="../LIN/LIN03"/>
</PartID>
<Qty>
<xsl:value-of select="FST/FST01"/>
</Qty>
<RequiredDate>
<xsl:value-of select="FST/FST04"/>
</RequiredDate>
<Data>
<xsl:value-of select="../../GROUP_1/N1/N101"/>
<xsl:value-of select="../../GROUP_1/N1/N102"/>
</Data>
</row>
</xsl:for-each>
</root>
</xsl:template>
</xsl:stylesheet>
|
|
next
|
Subject: Automated EDI to/from SQL - issues so far
Author: Steve R
Date: 22 Feb 2006 09:54 AM
|
My original post lost all of the line breaks when I edited it, so I will repost it here.
First I'll start by stating I may be trying to use the product for more difficult tasks than it was designed. I'm trying to avoid using the $15-$20K EDI translators (Gentran, Biztalk, etc). At first glance, it appeared Stylus Studio could potentially meet our requirements, which are:
1. Receive multiple ANSI X12 EDI files from ftp directory on an automated schedule
2. Translate each EDI file into multiple Microsoft SQL server tables w/ parent child relationships
3. Generate EDI file from multiple SQL tables on demand from web app
4. Notification of errors when they occur<br>
My issues so far with the above requirements are:
1. Is the best approach to get the EDI files from the ftp first in my own app then call Stylus with each file as a parameter to translate them?
2. Can all of the above steps be automated? Or are some of the functions required not in the API yet?
3. The Stylus X12 schemas limit the number of ISA's to one per file. This doesn't follow the standard. We receive multiple ISA's in each file.
4. Updating the database - I've read "Working with Relational Data as XML" (<a href="http://www.stylusstudio.com/docs/v62/d_rdb7.html">http://www.stylusstudio.com/docs/v62/d_rdb7.html</a>) several times. I'm not clear on exactly how to update the SQL server tables programmatically, or if it is even possible. From the steps involved, it appears that creating a MS SQLXML updategram would be easier. Am I missing something?
5. In the documentation, it says you can use a .rdbxml file anywhere you would use an xml file. I assume to map to the database, my XLST would map from my EDI file to the .rdbxml, however, an .rdbxml is not accepted as a source. How should I map this?
6. If the map fails for any reason, will I be able to know this programmatically? Will the entire transaction fail or will part of the record be written to the db?
If Stylus Studio is not yet ready or was not designed to handle this functionality, I understand. Although if it was, you sure could win over a lot of the traditional EDI translator users.
Thanks,
Steve
---
<?xml version='1.0' ?>
<xsl:template match="/">
<root>
<xsl:for-each select="X12/TS_830/GROUP_3/GROUP_7">
<row>
<PartID>
<xsl:value-of select="../LIN/LIN03"/>
</PartID>
<Qty>
<xsl:value-of select="FST/FST01"/>
</Qty>
<RequiredDate>
<xsl:value-of select="FST/FST04"/>
</RequiredDate>
<Data>
<xsl:value-of select="../../GROUP_1/N1/N101"/>
<xsl:value-of select="../../GROUP_1/N1/N102"/>
</Data>
</row>
</xsl:for-each>
</root>
</xsl:template>
</xsl:stylesheet>
|
 top
|
  Subject: Automated EDI to/from SQL - issues so far
Author: (Deleted User)
Date: 28 Feb 2006 11:55 AM
|
> 1. Is the best approach to get the EDI files from the ftp first in my own app then call Stylus with each file as a parameter to translate them?
The Stylus Studio adapters make it possible to connect directly to a remote URL. The following example shows the EDI adapter using a file from FTP as input.
adapter:EDI?ftp://localhost/root/ConvertToXML/831.x12
If you prefer, you can spell out default settings (or change them), as shown here:
adapter:EDI:newline=crlf:val=yes:decode=yes:field=yes:len=no:seg=yes:tbl=yes:typ=yes:opt=no:eol=yes?ftp://localhost/root/ConvertToXML/831.x12
> 2. Can all of the above steps be automated? Or are some of the functions required not in the API yet?
The Stylus Studio adapters are exposed as Java API, so definitely yes.
> 3. The Stylus X12 schemas limit the number of ISA's to one per file. This doesn't follow the standard. We receive multiple ISA's in each file.
The built-in EDI adapter now recognizes multiple ISA transactions in a single file. This will be available in the next Stylus Studio release.
> 4. Updating the database - I've read "Working with Relational Data as XML" (http://www.stylusstudio.com/docs/v62/d_rdb7.html) several times. I'm not clear on exactly how to update the SQL server tables programmatically, or if it is even possible. From the steps involved, it appears that creating a MS SQLXML updategram would be easier. Am I missing something?
You could create an XSLT/XQuery that uses an .rdbxml file as its output URL; any SQL/XML defined on the UPDATE tab could then be executed programmatically.
> 5. In the documentation, it says you can use a .rdbxml file anywhere you would use an xml file. I assume to map to the database, my XLST would map from my EDI file to the .rdbxml, however, an .rdbxml is not accepted as a source. How should I map this?
If the mapping is from EDI to DB the .rdbxml should be used as target schema. It would be interesting to see what your .rdbxml looks like.
> 6. If the map fails for any reason, will I be able to know this > programmatically? Will the entire transaction fail or will part of the record be written to the db?
It could be a good idea to configure a validation post step in the XSLT scenario.
Hope this helps.
David Foster
Stylus Studio Team
|
|
|
|
