XML import VBA 1

[micang]

Hi All,

Access 2007:

I would like to import xml files into an Access table using VBA.

Below is the structure of the xml file:

 <?xml version="1.0" ?> 
- <V55 xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance"[/URL] xsi:noNamespaceSchemaLocation="/smmt_share/v55_schema/xmlschema.xsd">
- <V55Header>
  <UniqueIdentifier>125010001002</UniqueIdentifier> 
  <V55FileType>1</V55FileType> 
  <FileCreatedDatetime>20100000000</FileCreatedDatetime> 
  <ScanDatetime>2010000000000</ScanDatetime> 
  <BatchID>SS0000001</BatchID> 
  <BatchSequence>002</BatchSequence> 
  <DataEntryDatetime>2010000000000</DataEntryDatetime> 
  </V55Header>
- <V55Detail>
  <DVLALocalOffice>000</DVLALocalOffice> 
  <Duplicate /> 
  <ReRegMarker>false</ReRegMarker> 
  <RegistrationMark>XXXXXX</RegistrationMark> 
  <RegMarkCheckDigit>H</RegMarkCheckDigit> 
  <TaxClassCode>00</TaxClassCode> 
  <Variant /> 
  <Version>EUROVCOMPLIANTENGINE</Version> 
  <EuroStatusDirectiveNumber /> 
  <TrackWidth>0000-0000----</TrackWidth> 
  </V55Detail>
  </V55>

I have searched online, but I can't figure out how to get the schema and then import the XML into Access using VBA.

Could someone possibly start me off on the correct path?

Many thanks.

Michael

 

[TheFitz]

This is probably not the way to do it, however, I didn't use a schema, I just read the XML file.

You'll probably need to hack the below to pieces, but it should give you a bit of a grounding:

Sub Load_XML_File(strFilename As String)
Dim objDomDoc      As New DOMDocument, objNodeData As IXMLDOMNode, intL1        As Integer
Dim intL2          As Integer, intL3               As Integer, intL4            As Integer
Dim strFileDate    As String, strPrevLoadID        As String, strCurLoadID      As String
Dim strTotalRecs   As String, strTableName         As String, strUpdateType     As String
Dim strKeyField    As String, strKeyValue          As String, aryUpdate()       As String
Dim intCounter     As Integer, rstParam            As DAO.Recordset, intPercent As Integer

sFile = strFilename

objDomDoc.async = False
objDomDoc.Load sFile

Set objNodeData = objDomDoc.documentElement.childNodes.Item(intL1)

If Err.Number = 91 Then
    Exit Function
Else
End If

Do Until intL1 < 0

    Set objNodeData = objDomDoc.documentElement.childNodes.Item(intL1)
    
    If intL1 < 4 Then
        
        Select Case objNodeData.nodeName
        Case "File_Creation_Date"
            strFileDate = objNodeData.Text
        Case "Previous_Load_ID"
            strPrevLoadID = objNodeData.Text
        Case "Current_Load_ID"
            strCurLoadID = objNodeData.Text
        Case "Total_Records"
            strTotalRecs = objNodeData.Text
            Load_XML_File = objNodeData.Text
        Case Else
            MsgBox "Unexpected node:" & objNodeData.nodeName
        End Select
        
    Else
        
        intL2 = 0
        
        Do Until intL2 < 0
    
            Set objNodeData = objDomDoc.documentElement.childNodes.Item(intL1).childNodes.Item(intL2)

            If intL2 < 4 Then
                Set objNodeData = objDomDoc.documentElement.childNodes.Item(intL1).childNodes.Item(intL2)
                
                Select Case objNodeData.nodeName
                Case "TableName"
                    strTableName = objNodeData.Text
                Case "UpdateType"
                    strUpdateType = objNodeData.Text
                Case "KeyFieldName"
                    strKeyField = objNodeData.Text
                Case "KeyFieldValue"
                    strKeyValue = objNodeData.Text
                Case Else
                    MsgBox "Unexpected node:" & objNodeData.nodeName
                End Select
                
            Else
                     
            End If
            
            Set objNodeData = objDomDoc.documentElement.childNodes.Item(intL1).childNodes.Item(intL2).nextSibling

            If objNodeData Is Nothing Then
                intL2 = -1
            Else
                intL2 = intL2 + 1
            End If

        Loop
        
        Call UpdateTableValues(strTableName, strUpdateType, strKeyField, strKeyValue, aryUpdate)
        
    End If
    
    Set objNodeData = objDomDoc.documentElement.childNodes.Item(intL1).nextSibling

    If objNodeData Is Nothing Then
        intL1 = -1
    Else
        intL1 = intL1 + 1
    End If
    
Loop

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[micang]

Hi TheFitz,

Thank you very much for your time.

I will use this a starting point, exactly what I needed.

Much appreciated.

Michael