ImportXML

[nmadrigal]

We receive XML files from a third-party that we import into Access on a
daily basis. I created a VB for importing this XML file based on the
test form we received. The VB does the following:

1. Converts the attribute-centric XML into element-centric XML using an
XSL
2. Imports the converted XML file into Access using ImportXML function.
This creates five separate tables "on the fly".
3. Imports data from the five tables into the main table Loans using an
append query.
4. Performs file and table cleanup

The problem we are now running into is this. The main Loan table and
the append query were created based on the the test file and all
possible fields. Now that we are receiving real data, not all of the
fields are present. When the module is called, we are receiving the
"Enter Parameter Value" when importing into the main table Loan for any
missing fields. If we enter through these messages, the VB errors with
"data type mismatch." I believe the "data type mismatch" error is
occuring because there are SQL replace statements in the append query
for some of the missing fields.

Is it possible to append data using VB instead of using an Access
query, doing the following:
a. Check if the field exists
b. If not, move on to next field
c. If so, copy data in that field from one table to another.

Thank you in advance.

 

[Danny J. Lesandrini]

As it turns out, I've done this with XML files using VBA code and there's an
article with sample download at the Database Journal website.
http://www.databasejournal.com/features/msaccess/article.php/3310901

I ended up creating a couple of functions that help me dynamically grab the
correct field name based on the node name in the XML. If one can't be found,
then (say, they added a new field I wasn't expecting, or renamed a node) then
that value would be skipped, but all data that can be mapped is added.

The first function signature looks like this ...
Private Function GetFieldName(sTable As String, sField As String) As String

Given the table it's going into, and the XML field name, the correct Access
table-field-name is returned. If one can't be found, then processing skips it.

The code for inserting the record uses a recordset based on the table, like this ...

' The WHERE clause simply returns an empty recordset, avoiding overhead.
sSQL = "SELECT * FROM tblProblems WHERE 1=0"
Set rst = dbs.OpenRecordset(sSQL, dbOpenDynaset)

' Create a new record
rst.AddNew

' The tblProblems table has 52 columns. The child nodes are processed in
' what ever order they come. The corresponding table field is looked
' up through the GetFieldName() function.
For ielement = 0 To 51
Set objNodeItem = objNode_DETAILS.childNodes(ielement)
If Nz(objNodeItem.Text, "") <> "" Then
sField = objNodeItem.nodeName
sColumn = GetFieldName("PRBLMS", sField)

' Here is where the recordset field is updated, but only if a field
' mapping could be found, and if a value exists.
If sValue <> "" And sColumn <> "" Then rst(sColumn) = sValue
End If
Set objNodeItem = Nothing
Next ielement

' Call the Update method to save the record.
rst.Update
rst.Close

You can download my code and step through it. It works pretty slick, but I'm
sure my code can be made more flexible and powerful, based on what you need.

 

[Danny J. Lesandrini]

Ooops. In an effort to simplify things, I cut out an important line of code.

My original line of code read like this, with a function to clean up the data:
sValue = URLDecode(objNodeItem.Text)

A more typical line would look like this
sValue = objNodeItem.Text

Without this line, the code below will, of course, always skip every field,
since the variable, sValue, will always be the empty string. My Bad.