Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Import XML with attributes in childnodes (Ms Access)

Posted on 2008-10-11
10
Medium Priority
?
2,464 Views
Last Modified: 2013-11-27
Dear Experts,

I'm trying to import an XML file like shown below into an ms access database.
All values should be stored in the same table (SGH_XML_RESPONSE)
Table :
XML_Response_Key (Int, Key, Autonum)
Ordernr (Txt)
Correct_Verwerkt (Txt)
ResultaatCode (Txt)
Omschrijving (Txt)
Response_Text (Memo, (Complete XML file stored as string))
AddDate (Date/Time)
Addwho (Txt)

XML File Example:

  <?xml version="1.0" ?>
- <Resultaat>
- <AfmeldResultaat OrderNr="SOR0513073" CorrectVerwerkt="false" ResultaatCode="KlantOngeldig">
  <Omschrijving>Gegevens op de melding SOR0513073 konden niet gevonden worden.</Omschrijving>
  </AfmeldResultaat>
- <AfmeldResultaat OrderNr="SOR0513145" CorrectVerwerkt="false" ResultaatCode="GlastypeOngeldig">
  <Omschrijving>Glastype iso494 kan niet gevonden worden.</Omschrijving>
  </AfmeldResultaat>
  </Resultaat>

The problem are the attributes in AfmeldResultaat (Attribute Ordernr,  CorrectVerwerkt and ResultaatCode).
Is there somebody who knows how to handle these attributed?

Code so far :

Function Call : fImportXML stXMLResponseString, "SGH_XML_RESPONSE"

Function fImportXML(strXML As String, strTableName As String)
   
    Dim xmlDOM  As DOMDocument
    Dim xmlNodeList As IXMLDOMNodeList
    Dim xmlNodeItem As IXMLDOMNode
    Dim xmlNodeField As IXMLDOMNode
    Dim rst As DAO.Recordset

    Set xmlDOM = New DOMDocument
    xmlDOM.LoadXML strXML

    Set xmlNodeList = xmlDOM.getElementsByTagName("OrderNr")
    Set rst = CurrentDb.OpenRecordset(strTableName)

    With rst
        For Each xmlNodeItem In xmlNodeList
            .AddNew
            For Each xmlNodeField In xmlNodeItem.ChildNodes
                .Fields(xmlNodeField.nodeName).Value = xmlNodeField.Text
            Next
            .Update
        Next
        .Close
    End With
End Function

Any help would be highly appriciated!

Thanks in advance
0
Comment
Question by:jrameuwissen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 2

Expert Comment

by:DrCabbage
ID: 22695641
Could you just add after the  existing ForEach another one to loop through the attributes as follows:

    For Each xmlNodeAttribute In xmlNodeItem.attributes
                .Fields(xmlNodeAttribute.Name).Value = xmlNodeAttribute.Value
    Next


0
 
LVL 1

Author Comment

by:jrameuwissen
ID: 22696755
Thanks for your reply DrCabbage.
I changed the function but the table is still not populated (also no errors though) :

Function fImportXML(strXML As String, strTableName As String)
   
    Dim xmlDOM  As DOMDocument
    Dim xmlNodeList As IXMLDOMNodeList
    Dim xmlNodeItem As IXMLDOMNode
    Dim xmlNodeField As IXMLDOMNode
    Dim xmlNodeAttribute As IXMLDOMAttribute
    Dim rst As DAO.Recordset

    Set xmlDOM = New DOMDocument
    xmlDOM.LoadXML strXML

    Set xmlNodeList = xmlDOM.getElementsByTagName("Afmeldresultaat")
    Set rst = CurrentDb.OpenRecordset(strTableName)

    With rst
        For Each xmlNodeItem In xmlNodeList
            .AddNew
            For Each xmlNodeField In xmlNodeItem.ChildNodes
                For Each xmlNodeAttribute In xmlNodeItem.Attributes
                    .Fields(xmlNodeAttribute.Name).Value = xmlNodeAttribute.Value
                Next
                .Fields(xmlNodeField.nodeName).Value = xmlNodeField.Text
            Next
            .Update
        Next
        .Close
    End With

End Function

Is there something I'm missing?

Thanks.
0
 
LVL 2

Expert Comment

by:DrCabbage
ID: 22699149
Sorry, I should have been clearer exactly where to put the second "For Each".

Because the attributes are on the outer node, that's what the foreach should be on - if you nest it as you have done, you're looping through the attributes on each child node (but the child nodes have no attributes). What I meant was to have a second foreach - after looping through the child nodes of AfmeldResultaat, we loop through the attributes of AfmeldResultaat:

With rst
        For Each xmlNodeItem In xmlNodeList
            .AddNew
            For Each xmlNodeField In xmlNodeItem.ChildNodes
                .Fields(xmlNodeField.nodeName).Value = xmlNodeField.Text
            Next
            For Each xmlNodeAttribute In xmlNodeItem.Attributes
                    .Fields(xmlNodeAttribute.Name).Value = xmlNodeAttribute.Value
             Next
            .Update
        Next
        .Close
    End With
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Author Comment

by:jrameuwissen
ID: 22700777
DrCabbage,

I must be missing something. I'm still not able to populate the table.
Still no errormessage occures...
Any ideas? Thanks in advance!

Function fImportXML(strXML As String, strTableName As String)
   
    Dim xmlDOM  As DOMDocument
    Dim xmlNodeList As IXMLDOMNodeList
    Dim xmlNodeItem As IXMLDOMNode
    Dim xmlNodeField As IXMLDOMNode
    Dim xmlNodeAttribute As IXMLDOMAttribute
    Dim rst As DAO.Recordset

    Set xmlDOM = New DOMDocument
    xmlDOM.LoadXML strXML
   
    Set xmlNodeList = xmlDOM.getElementsByTagName("AfmeldResultaat")
    Set rst = CurrentDb.OpenRecordset(strTableName)

    With rst
       For Each xmlNodeItem In xmlNodeList
           .AddNew
           For Each xmlNodeField In xmlNodeItem.ChildNodes
               .Fields(xmlNodeField.nodeName).Value = xmlNodeField.Text
           Next
           For Each xmlNodeAttribute In xmlNodeItem.Attributes
                   .Fields(xmlNodeAttribute.Name).Value = xmlNodeAttribute.Value
            Next
           .Update
       Next
       .Close
   End With


End Function
0
 
LVL 1

Author Comment

by:jrameuwissen
ID: 22701004
Could it be the getElementsByTagName tag (Afmeldresultaat) is incorrect?
Or maybe the string I pass to the function? The string consists out of the innertext element of the webbrowser....
0
 
LVL 2

Expert Comment

by:DrCabbage
ID: 22705109
It might be useful to set a breakpoint after the line

Set xmlNodeList = xmlDOM.getElementsByTagName("AfmeldResultaat")

and just check with the debugger that xmlNodeList has some entries (you can navigate through the DOM this way, which I've found very helpful for spotting bugs.

It might also be an idea to add a little error-checking code in case there is something wrong with the XML, for example :

 xmlDOM.LoadXML strXML
If (xmlDOM.parseError.errorCode <> 0) Then
     Dim myErr
     Set myErr = xmlDOM.parseError
     MsgBox("An XML Parse error occurred: " & myErr.reason)
End If

0
 
LVL 1

Author Comment

by:jrameuwissen
ID: 22705468
Thanks DrCabbage,

I added your code :

If (xmlDOM.parseError.errorCode <> 0) Then
    Dim myErr
    Set myErr = xmlDOM.parseError
    MsgBox("An XML Parse error occurred: " & myErr.reason)
End If

I receive errormessage : Invalid XML Declaration...
Any ideas?
0
 
LVL 2

Accepted Solution

by:
DrCabbage earned 2000 total points
ID: 22707431
Is your XML just like the example? You would need to remove the extra "-" signs, but otherwise it looks OK to me.

You can find out more of the parseError members at
http://msdn.microsoft.com/en-us/library/ms767720(VS.85).aspx

which would allow you to give the line where the error occurred - but the other thing I'm wondering is what encoding your file is in and whether you need an encoding declaration (or whether it works if you ditch the <?xml line altogether?
0
 
LVL 1

Author Comment

by:jrameuwissen
ID: 22709334
DrCabbage, you nailed it!!! I changed the code (see below) and it works just fine now!
Removing the <?xml line made it work.

Dim replaceLine As String
stXMLResponseString = Innertext
stXMLResponseString = Replace(stXMLResponseString, "-", "")
replaceLine = "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " ?>"
stXMLResponseString = Replace(stXMLResponseString, replaceLine, "")
fImportXML stXMLResponseString2, "SGH_XML_Response"

Function fImportXML(strXML As String, strTableName As String)
   
Dim xmlDOM  As DOMDocument
Dim xmlNodeList As IXMLDOMNodeList
Dim xmlNodeItem As IXMLDOMNode
Dim xmlNodeField As IXMLDOMNode
Dim xmlNodeAttribute As IXMLDOMAttribute
Dim rst As DAO.Recordset
Dim errordesc As String

    Set xmlDOM = New DOMDocument
    xmlDOM.LoadXML strXML
   
    If (xmlDOM.parseError.ErrorCode <> 0) Then
        Dim myErr
        Set myErr = xmlDOM.parseError
        MsgBox ("An XML Parse error occurred: " & myErr.reason)
        errordesc = "Application encountered an Error while trying to import XML response from Meldkamer in Function [fImportXML]" & vbNewLine & myErr
        Call ErrorLogging(errordesc, "IMPORT RESPONSE", Err.Number, Err.Description)
        Exit Function
    End If
   
    Set xmlNodeList = xmlDOM.getElementsByTagName("AfmeldResultaat")
    Set rst = CurrentDb.OpenRecordset(strTableName)

    With rst
       For Each xmlNodeItem In xmlNodeList
           .AddNew
           For Each xmlNodeField In xmlNodeItem.ChildNodes
               .Fields(xmlNodeField.nodeName).Value = xmlNodeField.Text
           Next
           For Each xmlNodeAttribute In xmlNodeItem.Attributes
                   .Fields(xmlNodeAttribute.Name).Value = xmlNodeAttribute.Value
            Next
            rst.Fields("Response_Text") = strXML
           .Update
       Next
       .Close
   End With

End Function

Thanks a lot for your help!
Points rewarded of course.
0
 
LVL 1

Author Comment

by:jrameuwissen
ID: 22709350
Oeps, the function call should be
fImportXML stXMLResponseString, "SGH_XML_Response"

Regards, Johan

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question