Solved

XML and Access 2002

Posted on 2008-10-02
7
202 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
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…
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

759 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

17 Experts available now in Live!

Get 1:1 Help Now