Import XML to Access ...

[Pawe³B]

Hi,
Under the structure of XML file.
In a given file can be 1000 <MIX> ...</ MIX> in any <MIX> may be the
a few to several <ingredient>.
From the standpoint of the database would be to import the data into two
tables
1. tblMix

2. tblIngredient (additional tables should be joined together one to many --
so that the data are connected to each other)

Theoretically, it seems about to learn - but I sit a little longer
(looking in the internet) .. and I can not prevail on this. Does anyone of
you przerabia³
import of XML to an Access VBA! ? how to deal with this?

An idea, a link to an example, how to effectively deal with this?

Paul

<MIXES>
<MIX>
<jobnumber>0001.02.2010</jobnumber>
<date>20100225</date>
<time>112244</time>
<mixsize>1</mixsize>
<mixunit>LITR</mixunit>
<mixcost>136,6</mixcost>
<voc>0</voc>
<ingredient>
<code>CRYSCLCP</code>
<amount>610,7</amount>
<density>1,018</density>
</ingredient>
<ingredient>
<code>H420</code>
<amount>223</amount>
<density>1,115</density>
</ingredient>
<ingredient>
<code>SC850</code>
<amount>175,4</amount>
<density>0,877</density>
</ingredient>
</MIX>
....
</MIXES>

 

[Albert D. Kallal]

What version of access are you using?

If you take your table as is and rename it with an xml extension, then it
does import into Access
(and, access will create two tables out of the data).

The problem is that ingredient part does not have a column connecting it
back to the JOB number table.

You could likely pre-process the text file to add that one needed column,
and then simply use the built in ms-access import, and that would save you
having to write the parsing part...

 

[Pawe³B]

Hi,
Access 2007,
Using imported from Access does not come into play. Unfortunately, but I
must import data into Access to connect to <Mix> with the <Ingredient>.
It seems to me that the VBA is the only chance...

Yours
Paul

 

[Albert D. Kallal]

Hi,
Access 2007,
Using imported from Access does not come into play. Unfortunately, but I
must import data into Access to connect to <Mix> with the <Ingredient>.
It seems to me that the VBA is the only chance...

Actually, I think you're misunderstanding my advice here. You really don't
want to write your own parsing and import routines to parse all of the xml.
what you'll do in these cases is still use the built in imports system, but
write VBA to drive that import.

So what I'm suggesting here is that 2007 has a pretty decent and good built
in xml importing ability. The idea here would be to leverage that import
utility. About the only thing we have to fix or change here is to insert the
correct job number into the ingredients table prior to the import. We could
also process the table data AFTER we do the import.

So in most kinds of imports of this nature, will still use the built in xml
importing ability, but will either post process, or pre-process the data
before we execute that import command.

Which the above two choices we use, will often depend much and how you
receive the data and what the format looks like.

If each xml file you received only had one job number, along with a bunch of
child records for the ingredients table, then this would be easy. We would:

1) execute a XML import command (in VBA code). This would thus pull the data
from the xml file, parse it out for us into two tables.

2) simply execute 2 append queries to insert or move that data out of the
two temporary tables into our main table(s). Of course the append query for
the child ingredients table would include the new primary key that we need
to insert so to correctly maintain the relationship back to the parent
table.

You can see how easy the above is. The above would only be a few lines of
code. The conceptual idea that I'm imparting to you is that we don't want to
write the xml parsing code ourselves. We want to use the built in xml
command to do most of the heavy lifting here and it can parse out the data
for us.

So now that you understand the conceptual idea here, we have two choices,
either we pre-process the xml data was some code to insert the correct child
linking code or data that we need for the ingredients table, or we executed
in port, and then read the xml file again with VBA code to determine how
many child lacquered sit in a row belong to one particular job number.

Our basic parse loop looks like:


Dim intF As Integer
Dim strF As String
Dim strData As String
Dim v As Variant
Dim rec As Variant
Dim xmlRec As Variant

strF = "c:\t.xml"
intF = FreeFile
Open strF For Input As intF
strData = input(LOF(intF), intF)
Close intF

' parse out each reocrd
v = Split(strData, "</mix>")
For Each xmlRec In v
rec = Split(xmlRec, "<mix>")(1)
Debug.Print "<mix>" & rec & "</mix>"
Open "c:\temp.xml" For Output As intF
Print #intF, "<mix>" & rec & "</mix>"
' xml import code here
Next

End Sub

The only part you need to add to the above is the xml transfer text command,
and two append queries, of which the 2nd one adds our related column back to
the 1st table.

 

[De Jager]

Hi,
Under the structure of XML file.
In a given file can be 1000 <MIX> ...</ MIX> in any <MIX> may be the
a few to several <ingredient>.
From the standpoint of the database would be to import the data into two
tables
1. tblMix

2. tblIngredient (additional tables should be joined together one to
many --
so that the data are connected to each other)

Theoretically, it seems about to learn - but I sit a little longer
(looking in the internet) .. and I can not prevail on this. Does anyone of
you przerabia³
import of XML to an Access VBA! ? how to deal with this?

An idea, a link to an example, how to effectively deal with this?

Paul

<MIXES>
<MIX>
<jobnumber>0001.02.2010</jobnumber>
<date>20100225</date>
<time>112244</time>
<mixsize>1</mixsize>
<mixunit>LITR</mixunit>
<mixcost>136,6</mixcost>
<voc>0</voc>
<ingredient>
<code>CRYSCLCP</code>
<amount>610,7</amount>
<density>1,018</density>
</ingredient>
<ingredient>
<code>H420</code>
<amount>223</amount>
<density>1,115</density>
</ingredient>
<ingredient>
<code>SC850</code>
<amount>175,4</amount>
<density>0,877</density>
</ingredient>
</MIX>
...
</MIXES>