Microsoft XML - Suggestions on Importing XML Doc

Author

Suggestions on Importing XML Doc

R Jones

2004-01-30, 2:29 am

All,

I'm trying to automate the import of an XML document into an Access
application. Unfortunately Access cannot properly read the document. When
it attempts to bring in the document it creates partial data structures and
does not import any data.

I have tried to bring this document into Visual Studio .NET but it also
errors out. The issue being the document uses nested elements, and these
elements are interpreted as tables and it bungs up VS.NET. (This is a
confirmed bug by Microsoft with no work arounds offered:
http://support.microsoft.com/defaul...kb;en-us;325696
)

So, ok, now the question is where do I start? Is there a third party
product I can call from Access that may be able to parse this file? Do I
have to write my own parser? Any suggestions would help.

And, PS, I have NO control over the creation of this XML document, so I
can't go to the source and ask for a Microsoft friendly XML formatted
document.

Suggestions?
Thanks,
Rick

Neil Smith

2004-01-30, 3:32 pm

Use XSL templates to transform your badly behaved XML document into
one suitable for Access. Just transform them in VB then import into
Access once you have verified the output format is correct.

Cheers - Neil.

On Thu, 29 Jan 2004 22:31:56 -0800, "R Jones"
<News@lunaconsulting.com> wrote:

quote:

>All,
>
>I'm trying to automate the import of an XML document into an Access
>application. Unfortunately Access cannot properly read the document. When
>it attempts to bring in the document it creates partial data structures and
>does not import any data.
>
>I have tried to bring this document into Visual Studio .NET but it also
>errors out. The issue being the document uses nested elements, and these
>elements are interpreted as tables and it bungs up VS.NET. (This is a
>confirmed bug by Microsoft with no work arounds offered:
>http://support.microsoft.com/defaul...kb;en-us;325696
> )
>
>So, ok, now the question is where do I start? Is there a third party
>product I can call from Access that may be able to parse this file? Do I
>have to write my own parser? Any suggestions would help.
>
>And, PS, I have NO control over the creation of this XML document, so I
>can't go to the source and ask for a Microsoft friendly XML formatted
>document.
>
>Suggestions?
>Thanks,
>Rick
>
>

========================================================
CaptionKit http://www.captionkit.com : Produce subtitled
internet media, transcripts and searchable video. Supports
Real Player, Quicktime and Windows Media Player.

VideoChat with friends online, get Freshly Toasted every
day at http://www.fresh-toast.net : NetMeeting solutions
for a connected world.

R Jones

2004-01-30, 6:30 pm

Just to clarify, if my goal is to persist the imported XML document into JET
tables would XSL templates still be the tool to use?

Another question: There are 30+ tables in this XML document with tons of
fields. Shouldn't there be an easy way (in Access) to import this document
and have it create the tables and populate them? It appears that Access
wants to do this but can't handle the XML structure.

Last question: What object model would I manipulate in VBA to automate the
import of the XML document? I'm looking for the simplest object, all I
really need to do is read the XML doc and have it create the populated
tables in an Access database.

Thanks for any help,
Rick

"Neil Smith" <neil@nospam.com> wrote in message
news:401aa786.2362426@news.blueyonder.co.uk...

quote:

> Use XSL templates to transform your badly behaved XML document into
> one suitable for Access. Just transform them in VB then import into
> Access once you have verified the output format is correct.
>
> Cheers - Neil.
>
> On Thu, 29 Jan 2004 22:31:56 -0800, "R Jones"
> <News@lunaconsulting.com> wrote:
>
When[QUOTE][color=darkred]
and[QUOTE][color=darkred]
>
> ========================================================
> CaptionKit http://www.captionkit.com : Produce subtitled
> internet media, transcripts and searchable video. Supports
> Real Player, Quicktime and Windows Media Player.
>
> VideoChat with friends online, get Freshly Toasted every
> day at http://www.fresh-toast.net : NetMeeting solutions
> for a connected world.

Neil Smith

2004-01-31, 8:28 am

On Fri, 30 Jan 2004 13:58:51 -0800, "R Jones"
<News@lunaconsulting.com> wrote:

quote:

>Just to clarify, if my goal is to persist the imported XML document into JET
>tables would XSL templates still be the tool to use?

Reckon so. To expand, the way I would approach this is to generate an
XML *export* of some data from Access. Then, use that as an outline, a
guide to what it will accept as an XML import format (we have to
presume that Access will read its own file formats ;-)

Finally, I would generate an XML transformation which converts the
input format, the data layout, into the format suitable for Access to
read it.

So you would go

Source document --> VB + XSL --> Output document

You might be able to persist the output document as a transofmed file,
or Access I think can read it as an ADO stream, a datasource -- you'll
have to read up on the syntax for that.

quote:

>Another question: There are 30+ tables in this XML document with tons of

Don't know. Thats out of our hands.

quote:

>Last question: What object model would I manipulate in VBA to automate the
>import of the XML document? I'm looking for the simplest object, all I
>really need to do is read the XML doc and have it create the populated
>tables in an Access database.

Maybe like here
http://www.visual-basic-data-mining...aspx?PostID=878

quote:

>
>Thanks for any help,
>Rick
>
>"Neil Smith" <neil@nospam.com> wrote in message
>news:401aa786.2362426@news.blueyonder.co.uk...
>When
>and
>
>