Posted by Dan Guzman on 11/18/06 18:38

It's true that you can transform EDI and GEDCOM files directly into
relational format. I think the reason XML is commonly used as an
intermediate format is that XML is perfect for hierarchical data and you can
leverage a high-performance XML import utility like SQLXML without writing
additional code. Although it will take a while, I expect XML will
eventually replace both EDI and GEDCOM formats. You'll be a step ahead if
you can process XML too.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Wes Groleau" <groleau+news@freeshell.org> wrote in message
news:uJv7h.211$9e.25@trnddc02...
> Ed Murphy wrote:
>> Dan Guzman wrote:
>>> Take a look at SQLXML Bulk Load
>>> (http://msdn2.microsoft.com/en-us/library/ms171993.aspx).
>>
>> He'd also need an EDI to XML translator. (I recognize those
>> damnable start/end tags.) Google indicates that several
>> translators exist; anyone want to offer a recommendation?
>
> I think I figured out a solution (haven't tried it yet).
>
> Comments on this idea welcome (I'm kind of new to SQL):
>
> The X12 files and GEDCOM files (maybe HL7, too?) have
> multiple levels. Generally, each "level X" record
> may own more than one record on level X+1
>
> So if a file has (data elem delims changed to spaces)
> ....
> CLP A B C
> SVC X Y Z
> SVC 1 2 3
> CLP D E F
> SVC P Q R
> SVC 5 6 7
> ....
> then the first pass through the file could create rows
>
> .... A B C X Y Z ....
> .... A B C 1 2 3 ....
> .... D E F P Q R ....
> .... D E F 5 6 7 ....
>
> Next, one query could SELECT DISTINCT to give
>
> .... A B C
> .... D E F
>
> while another could SELECT for
>
> .... A X Y Z ....
> .... A 1 2 3 ....
> .... D P Q R ....
> .... D 5 6 7 ....
>
> and the same strategy could be used on each adjacent pair of levels.
>
> Right ?
>
> --
> Wes Groleau
>
> He that is good for making excuses, is seldom good for anything else.
> -- Benjamin Franklin

[Back to original message]