Help importing XML Data

[Zen Masta]

I have an XML file with product information. I want to import all of this
data into one table. When I tried to import though, it created a table for
each group of identifiers.

This makes sense but is obviously no use to me. As you can see in the case
of the <measure> identifier, there are 3 but there could be 4...
depth,width,height,weight.. Or more simply, as you can see
<ProductIdentifier> has 2 items within, when I import ProductIdentifier is
made into its own table with fields for whatever items it had, ie
ProductIDType and IDValue etc.

My ultimate goal is to somehow import/update this information regularly into
an existing products table which has a lot of this information but in
different fields or combined fields. ie, where the xml file has 3 measure
elements, my table has 2, one for the trimsize with combines
lenght/width/height and another one just for the weight.

I think if I had some wysiwyg app to draw/link fields from the XML to my
existing table I could probably figure this out. Does anyone know of such a
tool or anything I can use to help me with this import? It doesn't have to
be free but you know, that would be cool too

Here is one complete record from the xml file:


<Product>
<RecordReference>7555081</RecordReference>
<NotificationType>03</NotificationType>
<ProductIdentifier>
<ProductIDType>02</ProductIDType>
<IDValue>0842355081</IDValue>
</ProductIdentifier>
<Barcode>10</Barcode>
<ProductForm>BC</ProductForm>
<ProductFormDetail>B305</ProductFormDetail>
<Title>
<TitleType>01</TitleType>
<TitleText>The Complete Book of When and Where</TitleText>
<TitlePrefix>The</TitlePrefix>
<TitleWithoutPrefix>Complete Book of When and
Where</TitleWithoutPrefix>
</Title>
<Website>
<WebsiteLink>http://www.tyndalebooksellers.com/productpage.asp?isbn=0-8423-5508-1</WebsiteLink>
</Website>
<Contributor>
<SequenceNumber>1</SequenceNumber>
<ContributorRole>A01</ContributorRole>
<PersonName>E. Michael Rusten</PersonName>
<PersonNameInverted>Rusten, E. Michael</PersonNameInverted>
</Contributor>
<Contributor>
<SequenceNumber>2</SequenceNumber>
<ContributorRole>A01</ContributorRole>
<PersonName>Sharon O. Rusten</PersonName>
<PersonNameInverted>Rusten, Sharon O.</PersonNameInverted>
</Contributor>
<NumberOfPages>560</NumberOfPages>
<MainSubject>
<MainSubjectSchemeIdentifier>33</MainSubjectSchemeIdentifier>
<SubjectCode>GNRGSEREF</SubjectCode>
</MainSubject>
<AudienceCode>01</AudienceCode>
<OtherText>
<TextTypeCode>01</TextTypeCode>
<TextFormat>02</TextFormat>
<Text>This popular collection of books not only provides key Bible
facts, character profiles, important places in history, and descriptive
timelines, but it makes learning enjoyable. &lt;p&gt;&lt;I&gt;The Complete
Book of Who's Who&lt;/I&gt; provides readers with a complete listing of
people in the Bible with descriptions of their lives and accomplishments.
&lt;p&gt;&lt;I&gt;The Complete Book of When and Where&lt;/I&gt; tells
intriguing accounts of 1,001 events in Christian history and their
significance. Includes a comprehensive timeline.</Text>
<TextSourceCorporate>Tyndale House Publishers</TextSourceCorporate>
</OtherText>
<OtherText>
<TextTypeCode>18</TextTypeCode>
<TextFormat>02</TextFormat>
<Text>&lt;b&gt;Understand quickly 1001 events that shaped our
world&lt;/b&gt;
&lt;p&gt;
&lt;i&gt;The Complete Book of When and Where&lt;/i&gt; focuses on 1001
events that shaped the religious consciousness of the world-specifically,
events in Jewish, Muslim, and Christian history.
&lt;p&gt;
It's a perfect reference tool for speakers and teachers.
&lt;br&gt;
Within minutes, you can understand the broad trends and major events of any
era of world history.
&lt;p&gt;
Entries give background and details surrounding events such as
&lt;ul&gt;
&lt;li&gt;1456, when Johannes Gutenberg first printed the 42-line Gutenberg
Bible, from the text of the Vulgate, Jerome's Latin translation of the Bible
from Hebrew and Greek.
&lt;li&gt;July 8, 1741, when Jonathan Edwards rocked New England with his
famous sermon "Sinners in the Hands of an Angry God."
&lt;li&gt;June 28, 1941, when World War I erupted following the
assassination of the Austro-Hungarian crown prince in Serbia.
&lt;/ul&gt;
This popular reference includes
&lt;ul&gt;
&lt;li&gt;Feature stories about important events, giving greater depth and
background
&lt;li&gt;Events from Jewish, Christian, and Muslim history
&lt;li&gt;A timeline that offers readers a quick view of historical events
in relation to one another
&lt;/ul&gt;
Learn about momentous events of the past that have shaped the world we live
in today.
</Text>
<TextSourceCorporate>Tyndale House Publishers</TextSourceCorporate>
</OtherText>
<Publisher>
<PublishingRole>01</PublishingRole>
<PublisherName>Tyndale House Publishers</PublisherName>
</Publisher>
<CityOfPublication>Carol Stream</CityOfPublication>
<CountryOfPublication>US</CountryOfPublication>
<PublishingStatus>04</PublishingStatus>
<PublicationDate>20050209</PublicationDate>
<CopyrightYear>2005</CopyrightYear>
<Measure>
<MeasureTypeCode>08</MeasureTypeCode>
<Measurement>1.3600</Measurement>
<MeasureUnitCode>lb</MeasureUnitCode>
</Measure>
<Measure>
<MeasureTypeCode>01</MeasureTypeCode>
<Measurement>8.2500</Measurement>
<MeasureUnitCode>in</MeasureUnitCode>
</Measure>
<Measure>
<MeasureTypeCode>02</MeasureTypeCode>
<Measurement>5.5000</Measurement>
<MeasureUnitCode>in</MeasureUnitCode>
</Measure>
<SupplyDetail>
<SupplierName>Tyndale House Publishers</SupplierName>
<TelephoneNumber>630-668-8905</TelephoneNumber>
<SupplierRole>01</SupplierRole>
<ReturnsCodeType>02</ReturnsCodeType>
<ReturnsCode>Y</ReturnsCode>
<ProductAvailability>20</ProductAvailability>
<PackQuantity>27</PackQuantity>
<Price>
<PriceTypeCode>01</PriceTypeCode>
<PriceAmount>14.97</PriceAmount>
</Price>
</SupplyDetail>
</Product>

 

[Dirk Goldgar]

I have an XML file with product information. I want to import all of
this
data into one table. When I tried to import though, it created a
table for
each group of identifiers.

This makes sense but is obviously no use to me. As you can see in the
case
of the <measure> identifier, there are 3 but there could be 4...
depth,width,height,weight.. Or more simply, as you can see
<ProductIdentifier> has 2 items within, when I import
ProductIdentifier is
made into its own table with fields for whatever items it had, ie
ProductIDType and IDValue etc.

My ultimate goal is to somehow import/update this information
regularly into
an existing products table which has a lot of this information but in
different fields or combined fields. ie, where the xml file has 3
measure
elements, my table has 2, one for the trimsize with combines
lenght/width/height and another one just for the weight.

I think if I had some wysiwyg app to draw/link fields from the XML to
my
existing table I could probably figure this out. Does anyone know of
such a
tool or anything I can use to help me with this import? It doesn't
have to
be free but you know, that would be cool too

Yes, importing complex XML using the standard Access import feature is
not much use, because it loses the relationships. I had to do this
recently and I wrote a function that used the MS XML parser (msxml.dll)
to parse the XML, and my won code to load the results into the tables I
had previously devised. At the time, I looked around for some simple
WYSIWYG tool to do it, and while I found some advertised, they either
didn't meet my needs or were too expensive -- I can't really remember,
but I didn't use any of them. Have a look yourself, by all means.
Maybe there's something better now.