[dba-Tech] XML to access?

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='&#x22;AD Business Name&#x22; ' 
AddressLine1='&#x22;AD Address Line 1&#x22; ' AddressLine2='&#x22;AD 
Address Line 2&#x22; '
         Suburb='&#x22;AD Suburb&#x22; ' State='&#x22;AD State&#x22; ' 
Postcode='&#x22;AD PostCode&#x22; ' PhoneNumber='&#x22;AD Phone 
Number&#x22; '
         Email='&#x22;AD Email&#x22; ' FaxNumber='&#x22;AD Fax 
Number&#x22; '/>
    <z:row BusinessName='&#x22;My Business Name&#x22; ' 
AddressLine1='&#x22;My Address Line 1&#x22; ' AddressLine2='&#x22; My 
Address Line 2&#x22; '
         Suburb='&#x22;My Suburb&#x22; ' State='&#x22;My State&#x22; ' 
Postcode='&#x22;MyPostCode&#x22; ' PhoneNumber='&#x22;My Phone 
Number&#x22; '
         Email='&#x22;MyEmail&#x22; ' FaxNumber='&#x22;My Fax 
Number&#x22; '/>
</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






More information about the dba-Tech mailing list