asked on

Generic XML Import for Access

Hello Experts,

I'm working on some XML data transfers between a website and an access database; the website, built in PHP, has pages which return an XML file (a web service, basically) for a given table of data. The XML is always "generic", like this:

<?xml version="1.0" encoding="iso-8859-1"?>
<export>
<row>
<somefield1>dadas</somefield1>
<somefield2>werwerwer</somefield2>
<somefield3>sdfsdfsdfsdfsdfsdf/somefield3>
</row>
<row>
<somefield1>dadas</somefield1>
<somefield2>werwerwer</somefield2>
<somefield3>sdfsdfsdfsdfsdfsdf/somefield3>
</row>
</export>

I use the msxml object to get the response, and now I want to create a generic function which will enumerate that xml, inserting or updating records as it goes.

The function should be called like this:

ImportXML(xmlhttp.responseText, "tblUsers", "UserID")

Private Function ImportXML(strXML as String, strTableName as String, strPKey as String)
'' Loop through each <row> element of strXML
'' Check for existence of strPKey
'' Update record if exists
'' Add New record if not
End Function

I've found lots of great examples already here on EE, but my head's starting to spin, and I'd really apprecaite some help building out this function!

Any ideas are greatly appreciated!

--Jon

PS, I'm not using Application.ImportXML because it has a bad habit of crashing access and giving no reason for it!

Leigh Purvis

Will the tags be named consistently with existing fields in the specified table?
What about the strPKey parameter? What's the relevance of passing that? (Do your tables use a natural key - or a surrogate like an autonumber?)

ASKER CERTIFIED SOLUTION

Leigh Purvis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jon Jaques

ASKER

Hello,

Yes, the fields will be named exactly the same in the XML as in the table, and all tables use an autonumber of the primary key...

Technically, the first field of each <row> will be the primary key field, but I thought it would make it easier to check for the pre-existence of a record if you could say something like this:

Dim RecordID as variant, strXMLValue as String
strXMLValue = xmlnode.value

RecordID = dlookup(strPKey, strTableName, strPKey & " = " & strXMLValue)

if RecordID > 0 then
'' Record already exists, update
else
'' Record does not exist, add new
end if

Thanks for the help!!!

Leigh Purvis

Ahh I see - so this isn't necessarily purely appending data - the possibility exists of it being edited records too?

Jon Jaques

ASKER

Yeah, man, I KNEW that code was supposed to be simple! Nice Job!

Your first layer there broke the XML down just the way I needed, although I must admit I have not tested it yet, but doing that now...

Jon Jaques

ASKER

Yes, editing; If data has changed on the site, I want to update the local records with that data.

Leigh Purvis

OK - now we're added a bit more complexity. (Thankfully ;-)

Function fImportXML(strXML As String, strTableName As String, strPKey As String)

Dim xmlDOM As DOMDocument
Dim xmlNodeList As IXMLDOMNodeList
Dim xmlNodeItem As IXMLDOMNode
Dim xmlNodeField As IXMLDOMNode
Dim rst As DAO.Recordset
Dim strDelim As String
Dim blnPK As Boolean
Dim blnAppend As Boolean
Dim intIndex As Integer

Set xmlDOM = New DOMDocument
xmlDOM.loadXML strXML

Set xmlNodeList = xmlDOM.getElementsByTagName("row")

If Len(strPKey) > 0 Then
For intIndex = 0 To xmlNodeList.Item(1).ChildNodes.Length - 1
If xmlNodeList.Item(1).ChildNodes(intIndex).nodeName = strPKey Then
blnPK = True
Exit For
End If
Next
End If

With CurrentDb.OpenRecordset(strTableName, dbOpenDynaset)
If blnPK Then
strDelim = IIf(.Fields(strPKey).Type = dbText, """", "")
End If
For Each xmlNodeItem In xmlNodeList
If blnPK Then
.FindFirst strPKey & "=" & strDelim & xmlNodeItem.ChildNodes(intIndex).Text & strDelim
If .NoMatch Then
blnAppend = True
.AddNew
Else
blnAppend = False
.Edit
End If
Else
.AddNew
End If
For Each xmlNodeField In xmlNodeItem.ChildNodes
If blnPK Then
If strPKey <> xmlNodeField.nodeName Or blnAppend Then
.Fields(xmlNodeField.nodeName).value = xmlNodeField.Text
End If
End If
Next
.Update
Next
.Close
End With

End Function

Jon Jaques

ASKER

ROCK ON!

YOU NAILED THIS!

Thank you so much!

Leigh Purvis

You're welcome. :-)

Jon Jaques

ASKER

This code is too good to keep to myself, and so I'm going to share it with my friends over on another forum -- do you have a preferred reference bit for credits at the top???? I'll also reference this thread.

Thanks again!

Leigh Purvis

Well I may not pilfer code from the greats - but I'll take inspiration from their copyright comment lines :-)
(i.e. Thanks to Dev and co and the Access Web)

'This code was originally written by Leigh Purvis.
'www.DatabaseDevelopment.co.uk
'It is not to be altered, distributed, coloured in or used for chips
'except as part of an application type thingy.
'You are free to use it in any application which doesn't suck,
'provided the copyright notice is left vaguely recognizable
'Or if you can think of something better to have in your modules instead
'you could shamelessly pretend to your boss that you came up with it :-)
'
'Code Courtesy of
'Leigh Purvis MSc MBCS ABC DEFG

Jon Jaques

ASKER

Hahahahaha, beautiful, I love it!

Jon Jaques

ASKER

I have another question regarding how to add authentication to this -- If I post another question, would you mind taking a look at it?

Leigh Purvis

I'll try to - of course someone else might offer help in the new question too - and that's rarely a bad thing.
After all - many voices make a choir :-)

(I can't sing though).