ACCESS FEED: Re: [MS_AccessPros] Re: Obtaining Data from a remote server

Hi Walter,

the XML file contains data
the XSD file contains the schema definition (mapping)

you need both files to import XML data that is not flat -- and the XSD file needs to be local. I put both files into a local directory to import xml.

I imported the data from your link into Access using 2010 -- several tables and records were created. What is missing, however, are the key fields to link the information -- this looks like just one record and its related records, so perhaps that is ok. The index table did not come in right -- the field names are records. If you can point me to the XSD file so I can grab it, I will see if it makes any difference.

Also, once the data goes into Access, you should rename fields -- several reserved words are being used

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

If you open an XML file with a browser, it will be indented and colored for you -- in case you haven't looked at it this way. I use NotePad++, a free tool that also indents and color-codes, and lets you edit.

~~~

here is an example of what an XSD file looks like:

<?xml version="1.0" ?>
<xs:schema id="MyEmployeeRecord" targetNamespace="http://MyEmployeeData/EmployeeRecord.xsd" xmlns:mstns="http://MyEmployeeData/EmployeeRecord.xsd" xmlns="http://MyEmployeeData/EmployeeRecord.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:element name="EmployeeRecord">
<xs:complexType mixed="true">
<xs:sequence>
<xs:element name="Department" type="xs:string" minOccurs="0" msdata:Ordinal="0" />

<xs:element name="Contact" minOccurs="0" maxOccurs="unbounded">
<xs:complexType mixed="true">
<xs:sequence>
<xs:element name="EmployeeID" type="xs:string" minOccurs="0" />
<xs:element name="EmpName" type="xs:string" minOccurs="0" />
<xs:element name="Region" type="xs:string" minOccurs="0" />
<xs:element name="Rate" type="xs:string" minOccurs="0" />

</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>

</xs:complexType>
</xs:element>
<xs:element name="MyEmployeeRecord" msdata:IsDataSet="true" msdata:EnforceConstraints="False">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element ref="EmployeeRecord" />
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>

~~~~~~~~~~~~~~~

as you can see, this is like the design view of a table -- it has the field definitions

Warm Regards,
Crystal

*
(: have an awesome day :)
*

--- On Wed, 6/1/11, nkpberk wrote:

> John;
> I get the same stuff without the tab delimited formatting.
> If the data were attached it would be the whole library? I
> need to know how to ask it a question to get a single
> publication record.
> Take a look (http://z3950.loc.gov:7090/voyager) maybe
> try to import it into a blank DB to see what it does. I
> looked all over the web and all I can find pertaining to
> Access & xml is importing and exporting one to the other
> but nothing to query the xml page for specific data.
> I'm in way over my head on this one.
> Walter
>
> --- "John Viescas" <john@...> wrote:
> >
> > Walter-
> >
> > I get it. What you got from that file is only a
> schema - no data. If you open
> > the XML file in Notepad, do you see any data?
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> >
> >
> > -----Original Message-----
> > From: nkpberk

> >
> > John;
> > What I posted is exactly what I got, No multiple
> columns.
> > Walter
> >
> > --- In MS_Access_Professionals@yahoogroups.com,
> "John Viescas" <john@> wrote:
> > >
> > > Walter-
> > >
> > > Ah, I see you said 12 *records* not 12
> columns. Are there multiple sets of
> > 12?
> > > Can you post a sample of the data?
> > >
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Paris, France)
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com]
> On Behalf Of nkpberk
> > > Sent: Tuesday, May 31, 2011 7:52 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] Re: Obtaining Data from
> a remote server
> > >
> > > John : Yes it is, but how do I ask for data based
> on ISBN or LCCN so I get
> > > actual info instead of the place holders?
> > > Walter
> > >
> > >
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com,
> "John Viescas" <john@> wrote:
> > > >
> > > > Walter-
> > > >
> > > > Isn't the info you need in the Map table?
> > > >
> > > > John Viescas, author
> > > > Microsoft Office Access 2010 Inside Out
> > > > Microsoft Office Access 2007 Inside Out
> > > > Building Microsoft Access Applications
> > > > Microsoft Office Access 2003 Inside Out
> > > > SQL Queries for Mere Mortals
> > > > http://www.viescas.com/
> > > > (Paris, France)
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: MS_Access_Professionals@yahoogroups.com
> > > > [mailto:MS_Access_Professionals@yahoogroups.com]
> On Behalf Of nkpberk
> > > > Sent: Tuesday, May 31, 2011 6:26 PM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: [MS_AccessPros] Re: Obtaining Data
> from a remote server
> > > >
> > > > John;
> > > > I downloaded the xml and came up with 9
> tables
> > > >
> > > > Configinfo; "Default" = 0
> > > >
> > > > DBInfo; "title"= LoC Gateway, "Description"
> = null
> > > >
> > > > Explain; "schemaInfo" = null
> > > >
> > > > ExplainResponse; "Version" = 1.1
> > > >
> > > > Index; (11 records under "Title") title,
> subject, creator, editor,
> > publisher,
> > > > description, date, name, ISBN, ISSN, LCCN
> > > >
> > > > IndexInfo; "Set" = null
> > > >
> > > > Map;(12 records under "Name") title,
> subject, creator, author, editor,
> > > > publisher, description, date, name, ISBN,
> ISSN, LCCN
> > > >
> > > > Record; "RecordSchema" =http://explain.z3950.org/dtd/2.0/ , "RecordPacking"
> > =
> > > > xml
> > > >
> > > > ServerInfo; "Host" = localhost ,"Port" =
> 7090, "database" = Voyager
> > > >
> > > > Quoted words are field names
> > > >
> > > > I am hoping to make this work by inputing
> The ISBN or LCCN (for older
> > > > publications without the ISBN.
> > > >
> > > > Any Idea how to make this work?
> > > >
> > > > Walter
> > > >
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com,
> "John Viescas" <john@>
> > wrote:
> > > > >
> > > > > Walter-
> > > > >
> > > > > In the latest versions of Access, you
> can Import an XML document into an
> > > > Access
> > > > > table. If you're able to download
> the XML document, try importing it to
> > see
> > > > if
> > > > > Access likes it.
> > > > >
> > > > > John Viescas, author
> > > > > Microsoft Office Access 2010 Inside
> Out
> > > > > Microsoft Office Access 2007 Inside
> Out
> > > > > Building Microsoft Access Applications
> > > > > Microsoft Office Access 2003 Inside
> Out
> > > > > SQL Queries for Mere Mortals
> > > > > http://www.viescas.com/
> > > > > (Paris, France)
> > > > >
> > > > >
> > > > > -----Original Message-----
> > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > [mailto:MS_Access_Professionals@yahoogroups.com]
> On Behalf Of nkpberk
> > > > > Sent: Monday, May 30, 2011 11:44 PM
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > Subject: [MS_AccessPros] Obtaining Data
> from a remote server
> > > > >
> > > > > Hello Group;
> > > > > I'm back with something a bit more
> complicated than usual. In my attempt
> > to
> > > > > build the(inventory) part of my
> application I want to be able to enter the
> > > > ISBN
> > > > > or LCCN from the Library of Congress to
> pull pertinent data for the
> > > > publications
> > > > > inventory table. I have the server link
> (z3950.loc.gov:7090/voyager) this
> > > > gives
> > > > > me an xml document but I have no idea
> how to query it or extract any
> > result.
> > > > > Any suggestions?
> > > > > Walter Griffin
> > > > >