Solved

XML and Access 2002

Posted on 2008-10-02
7
203 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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 …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

861 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now