XML Import in Access 2003

[David Sigmon]

I have been trying to import an xml document into Access
2003. I have been able to get it to import the data into
multiple tables that make seem to align with the .xml
format of the file. But any relations between the tables
has been lost. It is easy to see the relations in
the .xml document but no luck on import.
Any suggestions?

 

[Joe Fallon]

Are you creating new tables "on the fly" during the import?

Or do the tables already exist with pre-defined relationships and the import
is destroying them?

 

[Guest]

I have many files that i need to load into access tables.
I do not heve the tables or relationships created. I was
going to take the first XML file imported to establish the
tables and relationships. Access creates the tables and
puts the data into the tables but destroys the
relationships that you can see looking at the XML. If I
can get the tables and relationships established, I will
be appending many more XML files.

 

[Joe Fallon]

I think Access is making a "best guess" when it imports the XML files.
If it is "wrong" then it is up to you to correct the problem either in the
XML or in the Access tables (your choice.)

Try reversing the problem and build a correct Access database with some
sample data.
Then export it as XML.
Now check out the file structure.
Does the file you are trying to import look anything like that? (I doubt
it.)

Try importing the sample data and see if it goes into the right tables. If
it does then you know the correct format that Access is expecting.

1. Change your XML file to look like something Access is expecting.


2. You can leave your file "as is" but then when you import, consider the
tables it creates to be "staging tables".
Now write Append queries to move the data to the real tables.

 

[Mike Wachal [MSFT]]

Hi David and Joe,

Access 2003 (or 2002 for that matter) does not create relationships between
tables when importing from XML. As Joe suggested, when importing arbitrary
XML, Access makes a best guess as to how many tables should be created from
the XML. When importing Access created XML, you should get back the exact
same table structure that was used to export to XML, but the relationships
will be lost.

Regards,
Mike Wachal
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Joe Fallon]

Mike,
Please confirm:

1. If I have an existing table structure with proper relationships and I
export the data to XML and then re-import it the relationships are *still*
present.
(The import does not destroy the relationships.)

2. However, if I import the XML to a NEW .mdb file the tables are built but
the relationships are NOT built.
--
Joe Fallon
Access MVP

Hi David and Joe,

Access 2003 (or 2002 for that matter) does not create relationships between
tables when importing from XML. As Joe suggested, when importing arbitrary
XML, Access makes a best guess as to how many tables should be created from
the XML. When importing Access created XML, you should get back the exact
same table structure that was used to export to XML, but the relationships
will be lost.

Regards,
Mike Wachal
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Mike Wachal [MSFT]]

Hi Joe,

It's probably easier is I give you a set of scenarios and tell you what the
expected results in each case will be:

For each scenario, assume that I started with Table1 and Table2, which are
related to each other. I exported Table1 to XML (table1.xml) and selected
the option in the dialog to also export Table2 using the "Related Tables"
list.

Scenario1: Import table1.xml into my database and use the options to append
the data to the existing Table1 and Table2. (Assume there won't be any weird
key conflicts) The existing relationships between Table1 and Table2 are
preserved.

Scenario2: Import table1.xml in my database and use the option to import
structure and data, which is going to create two new tables. I end up with
tables named Table11 and Table21 (since Table1 and Table2 already exists),
there are no relationships between Table11 and Table21, but all the data is
there.

Scenario3: Import table1.xml into a blank database. I end up with Table1 and
Table2, but there are no relationships between the tables.

So the short answer is that there is no relationship information included in
an XML file, even when you choose to export schema. Since that information
is not in the file, it can not be recreated from the file.

Regards,
Mike Wachal
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Joe Fallon]

Thanks Mike.
BTW - you confirmed my scenarios.

It is exactly as I expected.
Thanks!
--
Joe Fallon
Access MVP

 

[will eichert]

I'm pretty new to this stuff, so forgive if this is a stupid question,
but I'm wondering why Access doesn't accept a schema file as a
parameter when you import an XML file, and use the relationships in
the schema file to map an XML file correctly to the database? The
schema file would certainly know about the relationships, especially
if you created it from the Access table(s) in the first place, no?

Thanks,

Will

 

[Mike Wachal]

Hi Will,

Access supports the use of schemas in two ways:

1) A separate schema file identified within the XML file as such:

<dataroot xmlnsd="urn:schemas-microsoft-comfficedata"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="Orders.xsd" generated="2003-11-21T17:48:23">

Note that Orders.xsd is specified as the schema file

2) Schema embedded directly in the XML file.

Access does not read relationship information from the schema nor it does
write relationship information when exporting to XML.

--
Regards,
Mike Wachal
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[will eichert]

OK, thanks for the info. I incorrectly assumed table relationships
would be in xml schemas.

Will