Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

XML and Access 2002

Posted on 2008-10-02
7
Medium Priority
?
211 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 2000 total points
ID: 22675419
Then it seem you would have better luck it the Table data was in XML.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

722 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