Posted by Wes Groleau on 11/18/06 04:11

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

• Next in forum: Re: strange results when calling a sql stored proc from within an Access 2000 project
• Prev in forum: Yellowfin leads the new players in taking business intelligence mainstream.
• Thread view: Re: Converting a file into multiple tables

[Reply to this message]