Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Help importing XML Data

Discussion in 'Information Technology' started by Zen Masta, Jul 28, 2006.

  1. Zen Masta

    Zen Masta
    Expand Collapse
    Guest

    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>
     
  2. Loading...


  3. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Zen Masta" <nospam@nospam.com> 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 <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.

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

    (please reply to the newsgroup)
     

Share This Page