Missing data when importing xml data into Access 2003

[RussR]

Hi,

I'm trying to do something extremely simple. I'm trying to take my xml file
which is:

<?xml version="1.0" encoding="UTF-8"?>
<Employees>
<Name>Russell</Name>
<SSN>123457890</SSN>
<Name>Robert</Name>
<SSN>456781234</SSN>
<Name>Richard</Name>
<SSN>123456789</SSN>
</Employees>

and import it into Access 2003. So I create a new blank database, File->Get
External Data->XML/XSD file types->Import all fields & data and then it does
it.

Problem is that when I look at the Employees table, it only has all the
fields, but only data entry. The data entry is the last one, Richard and
his SSN. Why? Where did the other two entries go?

It's such a simple xml file so I find it strange that it is not getting all
the data.

Please help.

Thanks

 

[Joe Fawcett]

Hi,

I'm trying to do something extremely simple. I'm trying to take my xml file
which is:

<?xml version="1.0" encoding="UTF-8"?>
<Employees>
<Name>Russell</Name>
<SSN>123457890</SSN>
<Name>Robert</Name>
<SSN>456781234</SSN>
<Name>Richard</Name>
<SSN>123456789</SSN>
</Employees>

and import it into Access 2003. So I create a new blank database, File->Get
External Data->XML/XSD file types->Import all fields & data and then it does
it.

Problem is that when I look at the Employees table, it only has all the
fields, but only data entry. The data entry is the last one, Richard and his
SSN. Why? Where did the other two entries go?

It's such a simple xml file so I find it strange that it is not getting all
the data.

Please help.

Thanks

I'm not sure but it's a poorly designed XML file. A structure such as this would
be easier to deal with:
<Employees>
<Employee>
<Name>Russel</Name>
<SSN>1234567890</SSN>
</Employee>
<Employee>
<Name>Robert</Name>
<SSN>456781234</SSN>
</Employee>
</Employees>

 

[RussR]

Thanks, can you tell me why my XML code is poor? Is it because the data for
each employee, i.e. name and ssn aren't properly encapsulated under the root
element?

How does the binding from XML to Access actually work? Root element is
table name, then what?

 

[Martin Honnen]

can you tell me why my XML code is poor? Is it because the data for
each employee, i.e. name and ssn aren't properly encapsulated under the root
element?

As Joe suggested if you want to have a <Name> element and a <SSN>
element belong together as the data of an employee then you should have
an <Employee> element encapsulating that data.
Putting everything as a child element of <Employees> as you had does not
make it clear that for instance <Name>Russell</Name> belongs to
<SSN>123457890</SSN>.
While processing sibling elements is possible it is usually much harder
and less efficient than accessing the child elements of a semantically
clearly named parent element.