Help importing XML Data

#1 (**permalink**) 28-Jul-2006, 08:43 AM

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 identifier, there are 3 but there could be 4...
depth,width,height,weight.. Or more simply, as you can see
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:

7555081
03

02
0842355081

10
BC
B305
 <TitleType>01</TitleType> <TitleText>The Complete Book of When and Where</TitleText> <TitlePrefix>The</TitlePrefix> <TitleWithoutPrefix>Complete Book of When and Where</TitleWithoutPrefix> 

http://www.tyndalebooksellers.com/productpage.asp?isbn=0-8423-5508-1

1
A01
E. Michael Rusten
Rusten, E. Michael

2
A01
Sharon O. Rusten
Rusten, Sharon O.

560

33
GNRGSEREF

01

01
02
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. The Complete
Book of Who's Who provides readers with a complete listing of
people in the Bible with descriptions of their lives and accomplishments.
The Complete Book of When and Where tells
intriguing accounts of 1,001 events in Christian history and their
significance. Includes a comprehensive timeline.
Tyndale House Publishers

18
02
Understand quickly 1001 events that shaped our
world

The Complete Book of When and Where focuses on 1001
events that shaped the religious consciousness of the world-specifically,
events in Jewish, Muslim, and Christian history.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/14069-help-importing-xml-data.html

It's a perfect reference tool for speakers and teachers.
 
Within minutes, you can understand the broad trends and major events of any
era of world history.

Entries give background and details surrounding events such as
<ul>
<li>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.
<li>July 8, 1741, when Jonathan Edwards rocked New England with his
famous sermon "Sinners in the Hands of an Angry God."
<li>June 28, 1941, when World War I erupted following the
assassination of the Austro-Hungarian crown prince in Serbia.
</ul>
This popular reference includes
<ul>
<li>Feature stories about important events, giving greater depth and
background
<li>Events from Jewish, Christian, and Muslim history
<li>A timeline that offers readers a quick view of historical events
in relation to one another
</ul>
Learn about momentous events of the past that have shaped the world we live
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14069
in today.

Tyndale House Publishers

01
Tyndale House Publishers

Carol Stream
US
04
20050209
2005

08
1.3600
lb

01
8.2500
in

02
5.5000
in

Tyndale House Publishers
630-668-8905
01
02
Y
20
27

01
14.97

#2 (**permalink**) 28-Jul-2006, 08:43 AM

"Zen Masta" wrote in message
news:OlVNvsasGHA.4596@TK2MSFTNGP04.phx.gbl
> 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 identifier, there are 3 but there could be 4...
> depth,width,height,weight.. Or more simply, as you can see
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14069
> 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)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14069
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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)