Importing XML bill of materials

[David G.]

I have an XML file containing structured bill of materials data.
Viewing the XML with "XML Notepad", the structure seems to be right.
(Assemblies are in a higher level then individual parts, and each
Assembly branch contains the proper list of parts.)

Access XML import produces 2 tables (Assemblies) and (Parts) as
expected; however, the resulting tables have no relationship, or
common filed. (Assembly records appear multiple times, 1 row for each
component; Components also appear multiple times, 1 row for each
assembly they are used in. )

Excel XML Import comes a little closer. But I get an error message
about "data contains a list of lists" when I try use the data. I
prefer to avoid manipulating the XML in Excel, just to open in Access
if possible.

The bill of materials structure is a common structure, so I'm
surprised I'm having this much trouble. I would appreciate any
comments, links, or suggestions!



THANKS!
David G.

 

[Tom van Stiphout]

On Mon, 02 Nov 2009 16:05:29 -0500, David G. <[email protected]>
wrote:

Create a query over both tables and export the data as XML. Is it in
the same format as your import file? No? Then I would not expect the
import will succeed.
Personally I would use the msxml parser and do this in VBA.
Sorry, there is no such thing as a "common structure".

-Tom.
Microsoft Access MVP

 

[David G.]

Tom:
Thanks for the help.
Regarding "common structure" I was referring to the basic structure
all BOM's follow:
Parent
-Child
-Child
-subParent
-subChild
Parent
-Child
....etc.

I will look into the MSXML approach.
THANKS!

Create a query over both tables and export the data as XML. Is it in
the same format as your import file? No? Then I would not expect the
import will succeed.
Personally I would use the msxml parser and do this in VBA.
Sorry, there is no such thing as a "common structure".

-Tom.
Microsoft Access MVP

THANKS!
David G.

 

[Bob Quintal]

Tom:
Thanks for the help.
Regarding "common structure" I was referring to the basic
structure all BOM's follow:
Parent
-Child
-Child
-subParent
-subChild
Parent
-Child
...etc.

I will look into the MSXML approach.
THANKS!

That is not the STRUCTURE of the BOM, that is the VIEW.

The structure is
ITEMS
ItemID (PK),
ItemName,
UnitOfMeasure,
Itemtype,
......

ProductStructure
Parent Item (FK into items)
Child Item (FK into Items)
FindNo
Qty
etc.

You use either a recursive SQL statement (not available in Access),
or a set of nested left joins to convert the structure into the view.

 

[Tom van Stiphout]

Right. What I was referring to is that there is no universally
accepted way to record this information in XML. For example I have
seen:
<product id=1 name=myProduct>
</product>
or
<product>
<id>1</id>
<name>myProduct</name>
</product>

This is referred to as the "elements vs attributes" discussion. See
e.g. http://www.ibm.com/developerworks/xml/library/x-eleatt.html
It's hard if not impossible for a generic tool to make sense of this.

-Tom.
Microsoft Access MVP