Could use a little direction...
-
One of our largest customers has decided to set up an EDI-like relationship with us to handle their purchase orders. Because of cost and lack of EDI knowledge, we have decided to use XML as our data vehicle. Our customer is going to send us an XML file containing their purchase orders, I need to import and shred that file into SQL Server 2005 so that we can then import it into our AS400 ordering system. (We can't go straight to the AS400 system because of the size of some of the data types and the length of the some field names.)
Anyway, I have been reading about using OPENXML and SQLXML as potential ways to import & shred the XML file and I could use some help in choosing which technology to pursue.
- I expect less than 100 orders per day (probably a lot less) but I will need to process them in the order that they arrive.
- I plan on importing the XML natively for historical purposes, and also shredding it into relational tables for the import into the AS400 system.
- The XML files should adhere to a schema that our customer will set up, so I am expecting to be able to use the Typed XML features.
- Originally, I thought that with the schema in the collection, SQL Server would shred the XML file into relational tables, but I think that I understand that shredding to be internal. This also leads me to think that I need to create a stored procedure or a view to map the XML elements to fields in the relational tables that I will need to create beforehand.
- We will probably have an import history table (with the native XML file), a header table and a detail table
- Like most projects for us, it has to be done yesterday...
I am really hoping that someone could give me a quick roadmap of the steps that I need to take.
Thanks for your time...
Sincerely,
Scott
I appreciate any help that you could afford
-
Scott,
sounds to me like you've already got most of the decisions on how to go about this sorted. I don't think you'll need to use OPENXML or SQLXML however if you're using SQL 2005, as you'll be able to use the native xquery functions built in.
Anyway, to address your points in turn:
- If you need to process them in the order they arrive I presume you're polling a location for the arrival of files ? If so then I would recommend a datetime stamp in the file name. I've been caught out in the past with using the created/modified timestamp after someone's edited a file. What are you using for polling and picking up the file ? I would suggest creating an SSIS package which periodically checks a file location and processes all the files it finds in sequence.
- You can pass the xml to a stored procedure as an xml parameter but this (I believe) does not preserve the source representation exactly. I would therefore suggest passing the xml document as an ntext parameter and sticking that in a table if you absolutely must preserve the source as is. Within this stored procedure you could then use either OPENXML to shred it into relational tables or convert it to an xml datatype parameter and use the built in functions.
- It's excellent you've established a proper schema, too often people set up a data exchange like this without one. You should validate the incoming document in SSIS using the schema before it ever touches your database. You can then raise an alert/email/log and stop processing the file. Obviously you can also enforce the schema on an xml datatype column within the database if the file passes validation.
- Yes, you have to shred them yourself.
- Not sure what you mean by header and detail table but logging when each file was loaded is definitely required
- I know the feeling
I don't feel that I've offered you much here other than recommending SSIS and a validation of your overall approach. If you've any more specific questions as you're developing it feel free to post.
HTH
-
Scott, do a search on the articles "Sales Order Workshop". It will give you some good examples in SQL 2005 on how to mass insert data from your Xml Documents.
-
Since 1999, I have not seen one EDI project profit from the use of XML. I have seen many revert back to a well defined fixed width or delimited file. I have seen several change from XML to fixed width/delimited file.
The fact is that XML does not assist you in EDI, it hampers your development process. It will cause you to develop tools along the way while complicating the entire process, and forcing work-arounds.
My recommendations:
A) define the file and record layout and transfer data more efficiently directly from the source and to the destination, bypassing all of the middle 'stuff'. This could be fixed width, but fixed width is probably better for 400 processes.
or
B) If you are determined to stay with xml, write an application that will read the xml, (i.e. in .Net), and insert records directly to the 400. Don't use the sql server as the middle man, it will only cost you more more in this scenario.
[font="Arial"]Clifton G. Collins III[/font]
-
Clifton has a valid point. I too have worked with EDI for over 8 years. EDI (usually ANSI x12) was developed for making transmissions across the "wire" smaller and essentially faster. However, EDI "standards" are almost never followed so even if you are developing an import of data from a "standard" such as ANSI x12 835 or something, the supplier of the data will never follow it correctly.
So, in my opinion Xml is much better even if it does have a slightly larger footprint (which can be accomodated by removing all WhiteSpace from the final Xml results). The reason why Xml is better is that all you'd need to do is create an Xslt Stylesheet to parse the results of any Xml file coming your way. You can then create different Xslt files depending on the version of your incoming data. This is the same as EDI except that as an EDI version changes, you typically need to rewrite your entire parser.
Clifton is correct that you shouldn't use the database for parsing the Xml. For one, it doesn't support Xslt. However, you can easily create a CLR Stored Procedure which can! The Stored Procedure will look at the version of the data coming in, load the appropriate Xslt, then use it to parse it into DataTables. Finally, the data will be inserted into the database in one Transaction. This will ensure there are no missing parts to the incoming data. If the import fails for some reason, well, you still have the file to attempt it again.
I really can't think of any PRO's that EDI is better than Xml except for size and the fact that it's supported by government standards. Which basically means you'll wait years and years until standards are finalized and made better for all.
-
From experience working with EDI (or, more correctly finding what went wrong when it broke)
1) Timestamp everything. Include a 'sending' date and time in every XML schema that you exchange. This will really really help you recover from a problem by allowing you to recreate the order of transmissions.
2) Unique identifier. Every transmission should have some kind of unique identifier so that you can refer to it explicitly.
3) Positive response. Send back an XML "acknowledgment" that includes the unique identifier of the transmission being acknowledged, and make them keep it. This response should also have its own 'sending' date and time and unique identifier in the schema.
4) Log everything at both ends, including that time stamp and unique identifier. This way you know exactly what was received, processed and replied to. At both ends.
Trust me, these suggestions will go a long way to help the health and well being of your hair follicles!
Steve (benairdunnat) G.
-
Yummy X12. I wrote an 837 (4010A1) parser in DTS back a few years ago. That was fun stuff. When I see XML being used in so many things that it is ill-equipped for, I think back to the X12 days. XML would indeed be well-suited for EDI.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply