MartyConnelly
martyconnelly at shaw.ca
Thu Sep 1 18:47:39 CDT 2005
For those of you with lower versions of Access below 2003, here is one
very simple method.
of reading a flat xml into an Access Table.
If nothing else the xml error handler for parsing is useful to find the
exact character
the parse fails on. Without it you may find yourself counting hundreds
of characters.
reminiscent of counting cobol printer output positioning without a ruler.
This method breaks if you are missing an xml element in a record or
element is out of order which is permissible in xml
unless enforced by a schema xsd file or the older DTD. So you would to
address individual xmldom nodes
via an array index and .getElementsByTagName method
You will also have to modify the XPath transform to suit your xml file.
'?LoadXMLFile("C:\XML\AdviserDetails.xml")
Public Function LoadXMLFile(ByRef AdviserXML As String)
On Error GoTo ErrorHandler
Dim oDOMDocument As MSXML2.DOMDocument40
Dim oNodeList As IXMLDOMNodeList
Dim oAdviserDetailsNode As IXMLDOMNode
Dim oLowestLevelNode As IXMLDOMElement
Dim objXMLDOMNamedNodeMap As IXMLDOMNamedNodeMap
Dim xPError As IXMLDOMParseError
Dim Mydb As Database
Dim MyRS As Recordset
Dim sTempValue As String
Dim lrec As Long
Dim lnorec As Long
Set oDOMDocument = New MSXML2.DOMDocument40
oDOMDocument.async = False
oDOMDocument.validateOnParse = True 'you may want to parse for errors
oDOMDocument.resolveExternals = False
oDOMDocument.preserveWhiteSpace = True
'use below if string or http xml file
' If Not oDOMDocument.LoadXML(AdviserXML) Then
' MsgBox ("XML File error")
' Call Err.Raise(ERR_UNABLE_TO_LOAD_ADVISER_XML,
,ERRDESC_UNABLE_TO_LOAD_ADVISER_XML)
'End If
'use if xml disk file
If Not oDOMDocument.Load(AdviserXML) Then
MsgBox ("XML File error")
Set xPError = oDOMDocument.parseError
DOMParseError xPError
End If
Set oAdviserDetailsNode = oDOMDocument.documentElement
'Set objXMLDOMNamedNodeMap = oAdviserDetailsNode.Attributes
Set Mydb = CurrentDb
Set MyRS = Mydb.OpenRecordset("NewTable")
'use appropriate XPath expression to select nodes
Set oNodeList = oAdviserDetailsNode.selectNodes("//BusinessDetails/*")
lnorec = 0
lrec = 0
Debug.Print oNodeList.length
MyRS.AddNew
For Each oLowestLevelNode In oNodeList
sTempValue = oLowestLevelNode.Text
lrec = lrec + 1
Select Case oLowestLevelNode.nodeName
Case "BusinessName"
MyRS!BusinessName = sTempValue
Case "AddressLine1"
MyRS!AddressLine1 = sTempValue
Case "AddressLine2"
MyRS!AddressLine2 = sTempValue
Case "Suburb"
MyRS!Suburb = sTempValue
Case "State"
MyRS!State = sTempValue
Debug.Print sTempValue
Case "Postcode"
MyRS!Postcode = sTempValue
Case "PhoneNumber"
MyRS!PhoneNumber = sTempValue
Case "Email"
MyRS!Email = sTempValue
Case "FaxNumber"
MyRS!FaxNumber = sTempValue
End Select
If lrec = 9 Then '9 elements in business details
MyRS.Update
lnorec = lnorec + 1
lrec = 0
MyRS.AddNew
End If
Next
MsgBox "Records Added=" & lnorec
Set MyRS = Nothing
Set Mydb = Nothing
Set oDOMDocument = Nothing
Set oAdviserDetailsNode = Nothing
Set objXMLDOMNamedNodeMap = Nothing
Exit Function
ErrorHandler:
' Call NewError.Raise(Err.Number, Err.Source, Err.Description)
End Function
Sub DOMParseError(xPE As IXMLDOMParseError)
' The document failed to load.
Dim strErrText As String
' Obtain the ParseError object
With xPE
strErrText = "Your XML Document failed to load" & _
"due the following error." & vbCrLf & _
"Error #: " & .errorCode & ": " & xPE.reason & _
"Line #: " & .Line & vbCrLf & _
"Line Position: " & .linepos & vbCrLf & _
"Position In File: " & .filepos & vbCrLf & _
"Source Text: " & .srcText & vbCrLf & _
"Document URL: " & .url
End With
Debug.Print strErrText
Dim s As String
Dim r As String
Dim i As Long
s = ""
For i = 1 To xPE.linepos - 1
s = s & " "
Next
r = "XML Error loading " & xPE.url & " * " & xPE.reason
Debug.Print r
'show character postion of error; tired of counting chars in xml file
If (xPE.Line > 0) Then
r = "at line " & xPE.Line & ", character " & xPE.linepos & vbCrLf & _
xPE.srcText & vbCrLf & s & "^"
End If
Debug.Print r
MsgBox strErrText, vbExclamation
End Sub
If you cut and paste this xml file below into notepad save as UTF-8
unicode format
otherwise the encoding statement wont agree with saved format
and the file BOM will be missing.
"AdviserDetails.xml"
<?xml version="1.0" encoding="utf-8"?>
<Root>
<AdviserDetails ID = "AD12345">
<BusinessDetails>
<BusinessName>"AD Business Name" </BusinessName>
<AddressLine1>"AD Address Line 1" </AddressLine1>
<AddressLine2>"AD Address Line 2" </AddressLine2>
<Suburb>"AD Suburb" </Suburb>
<State>"AD State" </State>
<Postcode>"AD PostCode" </Postcode>
<PhoneNumber>"AD Phone Number" </PhoneNumber>
<Email>"AD Email" </Email>
<FaxNumber>"AD Fax Number" </FaxNumber>
</BusinessDetails>
<BusinessDetails>
<BusinessName>"My Business Name" </BusinessName>
<AddressLine1>"My Address Line 1" </AddressLine1>
<AddressLine2>" My Address Line 2" </AddressLine2>
<Suburb>"My Suburb" </Suburb>
<State>"My State" </State>
<Postcode>"MyPostCode" </Postcode>
<PhoneNumber>"My Phone Number" </PhoneNumber>
<Email>"MyEmail" </Email>
<FaxNumber>"My Fax Number" </FaxNumber>
</BusinessDetails>
</AdviserDetails>
</Root
What the above file would look like as an ADTG or ADODB.RecordSet saved
to XML
<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly'>
<s:AttributeType name='BusinessName' rs:number='1'
rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='AddressLine1' rs:number='2'
rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='AddressLine2' rs:number='3'
rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='Suburb' rs:number='4' rs:nullable='true'
rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='State' rs:number='5' rs:nullable='true'
rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='Postcode' rs:number='6'
rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='PhoneNumber' rs:number='7'
rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='Email' rs:number='8' rs:nullable='true'
rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='FaxNumber' rs:number='9'
rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row BusinessName='"AD Business Name" '
AddressLine1='"AD Address Line 1" ' AddressLine2='"AD
Address Line 2" '
Suburb='"AD Suburb" ' State='"AD State" '
Postcode='"AD PostCode" ' PhoneNumber='"AD Phone
Number" '
Email='"AD Email" ' FaxNumber='"AD Fax
Number" '/>
<z:row BusinessName='"My Business Name" '
AddressLine1='"My Address Line 1" ' AddressLine2='" My
Address Line 2" '
Suburb='"My Suburb" ' State='"My State" '
Postcode='"MyPostCode" ' PhoneNumber='"My Phone
Number" '
Email='"MyEmail" ' FaxNumber='"My Fax
Number" '/>
</rs:data>
</xml>
Jon Tydda wrote:
>Hi all
>
>I've not used access much, but as the IT guy here, I've been asked to try
>and find a way to import data into an access db from an xml sheet, which is
>seemingly the only way we can oupput from the software we have.
>
>So in view of it being the blind leading the blind, as such, is there a
>SIMPLE way to do it?
>
>
>Jon
>
>
>
>
--
Marty Connelly
Victoria, B.C.
Canada