XML to Access: Simple Question 1

[Rydel]

I know XML has been around for ever, but in my own experience I have not yet had an opportunity to deal with it. So here comes a beginner's questions.

I have an XML file that has a format like this:

<something>
<comments>
<comment id="1" poster="2">
<comment id="2" poster="23">
<comment id="2" poster="22">
...
</comments>
<posters>
<poster id="2" user="ann">
<poster id="23" user="rydel">
...
</poster>
</something>

What would be the easiest and simplest way to convert the above XML file in an Access database with two respective tables comments (id, poster) / posters (id, user)?


regards,
rydel n23

http://pravapis.org/

http://rydel.net/

 

[PHV]

First, your xml file is badly formatted, should be:
<something>
<comments>
<comment id="1" poster="2"[highlight]/[/highlight]>
<comment id="2" poster="23"[highlight]/[/highlight]>
<comment id="2" poster="22"[highlight]/[/highlight]>
</comments>
<posters>
<poster id="2" user="ann"[highlight]/[/highlight]>
<poster id="23" user="rydel"[highlight]/[/highlight]>
</poster[highlight]s[/highlight]>
</something>
That said, depending of your version of access you may take a look at:
menu File -> External data -> Import
filter XML(*.xml, *.xsl)
The spelling may be different as I don't use an english version of office.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Rydel]

Sure, there are slashes at the end of each tag, I omitted them when I was typing it here...

I have an old version of MS Access which does not have XML support.

So is there some freeware tool for such purposes? Or maybe it's easy to do with DOM object from within a DHTML/ASP page?



regards,
rydel n23

http://pravapis.org/

http://rydel.net/

 

[PHV]

Take a look at the Microsoft XMLHTTP control.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Rydel]

So here's what happened. There are two new functions in MS Access 2002 ImportXML and ExportXML. It looks with ImportXML(DataSource, ImportOptions) the whole job would be just a couple lines of code in VBA. But unfortunately I have an old version of MS Access (2000). This function is not available there. So I had to do everything manually. I took the original XML file, separated it into two .TXT files and then with several GREP replacements turned the XML file into a simple CSV text file, which I then manually imported into two Access tables... I guess I could've given a star to myself...


regards,
rydel n23

http://pravapis.org/

http://rydel.net/