Solved

XML and Access 2002

Posted on 2008-10-02
7
205 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
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

809 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