XML to Access

[arkins]

All,

I have been "given" 225 XML files and no schema, and have been asked
to load the data from the XML files into an existing Access DB.

Since I "understand" the data in the XML file, I know there's a "long
way" I can do it by bringing the data into Excel, shifting it around
as needed, and then moving it into Access.

But there has to be a faster way...

I've tried a couple of XML tools, but they all ask me for my schema,
which I don't have....

What's the easiest way to do what I need to do? Is there a tool to
create a schema from an XML file?

Thanks,

Patrick

 

[Peter Flynn]

All,

I have been "given" 225 XML files and no schema, and have been asked
to load the data from the XML files into an existing Access DB.

Since I "understand" the data in the XML file, I know there's a "long
way" I can do it by bringing the data into Excel, shifting it around
as needed, and then moving it into Access.

But there has to be a faster way...

I've tried a couple of XML tools, but they all ask me for my schema,
which I don't have....

What's the easiest way to do what I need to do? Is there a tool to
create a schema from an XML file?

Several, but if the documents are already valid, you shouldn't need the
schema just to transform them.

This kind of bulk processing is best done with a commandline processor
like Saxon, and a simple scripted loop. Remove any mention of the schema
from the documents (if there is one) using a stream editor before the
document is passed to the processor.

///Peter

 

[Martin Honnen]

I've tried a couple of XML tools, but they all ask me for my schema,
which I don't have....

What's the easiest way to do what I need to do? Is there a tool to
create a schema from an XML file?

The .NET framework SDK has a tool xsd.exe which can infer a W3C XML
Schema from an XML instance document. Visual Studio 2005 can also do that.
I am sure several XML editors out there can do that too.
Note that I don't know what kind of schema Access needs to import XML data.

 

[Patrick A]

Thanks for the suggestions, I'll keep at it.

 

[Ken Starks]

Thanks for the suggestions, I'll keep at it.

I use 'Oxygen' as my XML editor, and I'm very happy with it.
(look at http://www.oxygenxml.com/documentation.html#demo_videos)


It will produce a Schema for you from a set of sample documents, using
a tool it calls the Trang convertor. It will aslo produce one from a DTD
if that is what your XML currently has.

For serious work, you would need to tweak the Schema by hand to
constrain the data exactly as strictly as you need, but no more,
and you would want to annotate it. Or you might have to unconstrain it,
making certain elements or attributes optional or of a wider type.


Access will produce a Schema for its own version of XML, which is the
one it wants to import. It isn't as studidly complex as the schemas you
get for Excel and word, which jumble together presentation and content.
The Access schemas for simple card-index type database tables are
refreshingly clean and simple for MS.

Depending on the complexity of the Transformation from your XML to the
Access version, I would suggest either straight-forward XSLT (eg using
saxon as Peter Flynn suggested) or in Python's using 4Suite, which is
really nice to work with, It knows XPath 1.0, which is the same as the
free version of Saxon, I believe. (http://4suite.org/index.xhtml)

If you are cautious, you should validate the output XML before feeding
it to your existing database. Unfortunately 4Suite does not validate
against Schemas, another reason for using Oxygen.

Oxygen has a free trial which should last long enough for you to
complete the task. Go straight to the Menubar --> Tools --> Trang
Converter, and you will have your Schema and rather nice html
documentation for it in next to no time. Well, perhaps not quite that
fast if you provide Oxygen with the whole batch of 255 files.

You can get an appropriate heading for your xml by going to Menubar -->
File --> New... --> XML Document --> and filling in the Dialogue box
with your Schema. You might want to choose a namespace at this stage
if your 255 files don't already have one.

 

[Patrick A]

Thanks, I didn't make much headway yesterday - just a long file that
looked like a notepad version of my XML, so I'll keep plugging at it
with all these options for awhile longer before I give up and start
importing things into Excel.