Missing data when importing xml data into Access 2003

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RussR - 29 Jul 2005 15:25 GMT

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

Reply to this Message

Joe Fawcett - 29 Jul 2005 17:10 GMT

> Hi,
>
[quoted text clipped - 25 lines]
>
> 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>

Signature

Joe (MVP - XML)

https://mvp.support.microsoft.com/profile=8AA9D5F5-E1C2-44C7-BCE8-8741D22D17A5

Reply to this Message

RussR - 29 Jul 2005 18:53 GMT

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?

>> Hi,
>>
[quoted text clipped - 38 lines]
> </Employee>
> </Employees>

Reply to this Message

Martin Honnen - 29 Jul 2005 19:12 GMT

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

Signature

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

Reply to this Message