Solved

XML and Access 2002

Posted on 2008-10-02
7
206 Views
Last Modified: 2013-11-29
I have a XML file that I would like to import into Access as a table When I try only the structure imprts not the data and yes I have pushed the structure and data option Here is the XML
 <?xml version="1.0" encoding="UTF-8" ?>
- <topmostSubform>
  <f1_01_0_>1</f1_01_0_>
  <f1_02_0_>1</f1_02_0_>
  <f1_03_0_>1</f1_03_0_>
  <f1_04_0_>1</f1_04_0_>
  <f1_05_0_>1</f1_05_0_>
  <f1_06_0_>1</f1_06_0_>
  <f1_07_0_>1</f1_07_0_>
  <f1_08_0_>1</f1_08_0_>
  <f1_09_0_>Jane X</f1_09_0_>
  <f1_10_0_>Doe</f1_10_0_>
  <f1_11_0_>123</f1_11_0_>
  <f1_12_0_>00</f1_12_0_>
  <f1_13_0_>0000</f1_13_0_>
  <f1_14_0_>123 Main St</f1_14_0_>
  <f1_15_0_>Chicago,Il 60606</f1_15_0_>
  <c1_01_0_>Yes</c1_01_0_>
  <c1_02_0_>Off</c1_02_0_>
  <c1_03_0_>Off</c1_03_0_>
  <c1_04_0_>Yes</c1_04_0_>
  <f1_16_0_>15</f1_16_0_>
  <f1_17_0_>75</f1_17_0_>
  <f1_18_0_>Exempt</f1_18_0_>
  <f1_19_0_ />
  <f1_20_0_>wba</f1_20_0_>
  <f1_21_0_ />
  <f1_22_0_ />
  <f2_01_0_ />
  <f2_02_0_ />
  <f2_03_0_ />
  <f2_04_0_ />
  <f2_05_0_ />
  <f2_06_0_ />
  <f2_07_0_ />
  <f2_08_0_ />
  <f2_09_0_ />
  <f2_10_0_ />
  <f2_11_0_ />
  <f2_12_0_ />
  <f2_13_0_ />
  <f2_14_0_ />
  <f2_15_0_ />
  <f2_16_0_ />
  <f2_17_0_ />
  <f2_18_0_ />
  <f2_19_0_ />
  </topmostSubform>
0
Comment
Question by:Serena2345
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22626239
Serena2345,

When I export to XML with the default options, two files are created.
An XML file and an XSD file.

When I import th XML file, it works fine.
I have attached the XML and XSD files for a simple, two record, employee table.

See if you can detect any differences between my XML file and yours.
Do you have an XSD file as well?
I notice that your missing the "<dataroot..." line.

JeffCoachman
The XML file:
 
<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xsi:noNamespaceSchemaLocation="tblEmployees.xsd" generated="2008-10-02T13:02:33">
<tblEmployees>
<EmpID>1</EmpID>
<FName>Joe</FName>
<LName>Klecko</LName>
<DOB>1960-02-05T00:00:00</DOB>
<Salary>55000</Salary>
</tblEmployees>
<tblEmployees>
<EmpID>2</EmpID>
<FName>Karen</FName>
<LName>Jones</LName>
<DOB>1964-08-22T00:00:00</DOB>
<Salary>75000</Salary>
</tblEmployees>
</dataroot>
 
 
The XSD file:
 
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:od="urn:schemas-microsoft-com:officedata">
<xsd:element name="dataroot">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="tblEmployees" minOccurs="0" maxOccurs="unbounded"/>
</xsd:sequence>
<xsd:attribute name="generated" type="xsd:dateTime"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="tblEmployees">
<xsd:annotation>
<xsd:appinfo>
<od:index index-name="PrimaryKey" index-key="EmpID " primary="yes" unique="yes" clustered="no"/>
<od:index index-name="CustID" index-key="EmpID " primary="no" unique="no" clustered="no"/>
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="EmpID" minOccurs="1" od:jetType="autonumber" od:sqlSType="int" od:autoUnique="yes" od:nonNullable="yes" type="xsd:int"/>
<xsd:element name="FName" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="25"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="LName" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="25"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="DOB" minOccurs="0" od:jetType="datetime" od:sqlSType="datetime" type="xsd:dateTime"/>
<xsd:element name="Salary" minOccurs="0" od:jetType="currency" od:sqlSType="money" type="xsd:double"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

Open in new window

0
 

Author Comment

by:Serena2345
ID: 22629079
no I do not have an xsd file how do I build one
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22636447
Serena2345,


"how do I build one "

It is created when you export a table to XML

Where did this file come from?

JeffCoachman
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Serena2345
ID: 22649725
It was exported data from Adobe acrobat
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22652983
Serena2345,

Then I am even more confused.

You don't just "exported data from Adobe acrobat".
Adobe Acrobat creates PDF Files.
So are you saying you exported a PDF File to XML?
Yes or No.

If Yes, then what did you create the PDF File from?
Because your XML file has the term: "<topmostSubform>" ,in it.
This seems to indicate that you exported a *Form*, not a *Table*.
As far as I know, you cannot import an XML *form* as an Access *table*.

Can you confirm all of these points.

Thanks

JeffCoachman
0
 

Author Comment

by:Serena2345
ID: 22668718
Yes you are right
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 22675419
Then it seem you would have better luck it the Table data was in XML.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question