locked
Importing XML into Access 2003 RRS feed

  • Question

  •  I'm trying to import a multi-level XML file into Access2003. Below is the structure of the file.

    Document
      -xml version="1.0" encoding="utf-8"
      -ns0:Root
        -DP
          -Members
            -Member
              +Gender
              +Name
              +Plan
              -Services
                -Service
                  -Name
                    "Service 1"
                  -Name
                    "Service 2"
                  -Name
                    "Service 3"
            +Member
            +Member

    When I import the file, Access is creating the Member table and Service table but only "Service 3" is being imported, the previous ones are being ignored. How do I import this file to get all the services associated with the member?

    Friday, November 22, 2013 9:42 PM

Answers

  • Hi.

    Welcome to MSDN forum.

    I made a similar XML file and met the same issue as you mentioned.

    According to the samples form Access 2003 and XML, there are several XML files only contain single-level XML structures. I don’t think we can import a multi-level XML file into Access and Access will generate several tables contain the mapping relation. It related to the relation of parent table and sub table. Access cannot support at present. If you need this new feature in Access, I recommend you to submit a feedback from Submit Feedback for Office.

    For the workaround, I suggest you splitting current structure. If they are related, you need to create the relationship with these two types (Primary keys and foreign keys). After that, import them into Access.

    For example:

    <?xml version="1.0"?>
    <so id="1134" type="Rush">
      <Members>
        <Member>
          <Gender>1234</Gender>
          <Name>4</Name>
          <Plan>5</Plan>
        </Member>
        <Member>
          <Gender>Axyz</Gender>
          <Name>5</Name>
          <Plan>5</Plan>
        </Member>
      </Members>
    <Services>
    <Service>
    <Name>Service 1</Name>
    <Gender>1234</Gender>
    </Service>
    <Service>
    <Name>Service 2</Name>
    <Gender>Axyz</Gender>
    </Service>
    </Services>
    </so>
    Hope this could give you help.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Edited by George Hua Monday, November 25, 2013 3:37 PM
    • Marked as answer by George Hua Tuesday, December 3, 2013 3:17 AM
    Monday, November 25, 2013 3:35 PM
  • Here is one workaround for reading a multi-level xml doc with VBA -- note: my sample reads every node, so what I am doing (is actually just writing to the debug window here -- but what I would do is to -- ) is to write to a basic "Raw Data" table (using DAO c ode) that would just consist of two fields/columns -- one of type Text to receive the Node Name and one of type memo that would receive the Node Value (content). Once you have added your xml data this "Raw Data" table you will be able to see the repetitive Node Names and be able to pull out the values:

    Sub ReadXml()
    '--using Library reference to Microsoft XML, v6.0 -- v3.0 might work as well
    
       Dim xmlDoc As MSXML2.DOMDocument, xmlNode As MSXML2.IXMLDOMNode
       Dim xmlNodeList As MSXML2.IXMLDOMNodeList
       Dim myNode As MSXML2.IXMLDOMNode, node2 As MSXML2.IXMLDOMNode, readXmlDoc As String
       Dim j As Integer
       On Error GoTo errlbl1
    
       readXmlDoc = "C:\Code\testAcc\catalog.xml"
       
       Set xmlDoc = New MSXML2.DOMDocument
       xmlDoc.async = False
       xmlDoc.Load (readXmlDoc)
       
       Set xmlNodeList = xmlDoc.getElementsByTagName("*")
       j = 1
       Debug.Print "Count of Notes: " & xmlNodeList.length
       For Each xmlNode In xmlNodeList
          For Each myNode In xmlNode.childNodes
             Debug.Print j & ") " & myNode.Attributes(0).Text & " *** " & myNode.nodeName
             For Each node2 In myNode.childNodes
                Debug.Print "      " & node2.nodeName & " ... " & node2.nodeTypedValue
    errlbl1:
                'Debug.Print "Err = " & Err
                If Err <> 0 Then
                   Debug.Print "Exiting Inner For Loop"
                   GoTo errLbl2
                End If
             Next
             j = j + 1
          Next
       Next
    errLbl2:
       Set xmlDoc = Nothing
       Debug.Print "Done!"
       
    End Sub


    Rich P


    • Edited by Rich P123 Monday, November 25, 2013 4:26 PM ,,,,,,
    • Marked as answer by George Hua Tuesday, December 3, 2013 2:53 AM
    Monday, November 25, 2013 4:25 PM

All replies

  • Hi.

    Welcome to MSDN forum.

    I made a similar XML file and met the same issue as you mentioned.

    According to the samples form Access 2003 and XML, there are several XML files only contain single-level XML structures. I don’t think we can import a multi-level XML file into Access and Access will generate several tables contain the mapping relation. It related to the relation of parent table and sub table. Access cannot support at present. If you need this new feature in Access, I recommend you to submit a feedback from Submit Feedback for Office.

    For the workaround, I suggest you splitting current structure. If they are related, you need to create the relationship with these two types (Primary keys and foreign keys). After that, import them into Access.

    For example:

    <?xml version="1.0"?>
    <so id="1134" type="Rush">
      <Members>
        <Member>
          <Gender>1234</Gender>
          <Name>4</Name>
          <Plan>5</Plan>
        </Member>
        <Member>
          <Gender>Axyz</Gender>
          <Name>5</Name>
          <Plan>5</Plan>
        </Member>
      </Members>
    <Services>
    <Service>
    <Name>Service 1</Name>
    <Gender>1234</Gender>
    </Service>
    <Service>
    <Name>Service 2</Name>
    <Gender>Axyz</Gender>
    </Service>
    </Services>
    </so>
    Hope this could give you help.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Edited by George Hua Monday, November 25, 2013 3:37 PM
    • Marked as answer by George Hua Tuesday, December 3, 2013 3:17 AM
    Monday, November 25, 2013 3:35 PM
  • Here is one workaround for reading a multi-level xml doc with VBA -- note: my sample reads every node, so what I am doing (is actually just writing to the debug window here -- but what I would do is to -- ) is to write to a basic "Raw Data" table (using DAO c ode) that would just consist of two fields/columns -- one of type Text to receive the Node Name and one of type memo that would receive the Node Value (content). Once you have added your xml data this "Raw Data" table you will be able to see the repetitive Node Names and be able to pull out the values:

    Sub ReadXml()
    '--using Library reference to Microsoft XML, v6.0 -- v3.0 might work as well
    
       Dim xmlDoc As MSXML2.DOMDocument, xmlNode As MSXML2.IXMLDOMNode
       Dim xmlNodeList As MSXML2.IXMLDOMNodeList
       Dim myNode As MSXML2.IXMLDOMNode, node2 As MSXML2.IXMLDOMNode, readXmlDoc As String
       Dim j As Integer
       On Error GoTo errlbl1
    
       readXmlDoc = "C:\Code\testAcc\catalog.xml"
       
       Set xmlDoc = New MSXML2.DOMDocument
       xmlDoc.async = False
       xmlDoc.Load (readXmlDoc)
       
       Set xmlNodeList = xmlDoc.getElementsByTagName("*")
       j = 1
       Debug.Print "Count of Notes: " & xmlNodeList.length
       For Each xmlNode In xmlNodeList
          For Each myNode In xmlNode.childNodes
             Debug.Print j & ") " & myNode.Attributes(0).Text & " *** " & myNode.nodeName
             For Each node2 In myNode.childNodes
                Debug.Print "      " & node2.nodeName & " ... " & node2.nodeTypedValue
    errlbl1:
                'Debug.Print "Err = " & Err
                If Err <> 0 Then
                   Debug.Print "Exiting Inner For Loop"
                   GoTo errLbl2
                End If
             Next
             j = j + 1
          Next
       Next
    errLbl2:
       Set xmlDoc = Nothing
       Debug.Print "Done!"
       
    End Sub


    Rich P


    • Edited by Rich P123 Monday, November 25, 2013 4:26 PM ,,,,,,
    • Marked as answer by George Hua Tuesday, December 3, 2013 2:53 AM
    Monday, November 25, 2013 4:25 PM
  • Thanks Rich,

    I'm working through your code but am getting an error.

    Run-time error 91, Object variable or With block variable not set.

    It's happening on this line:

    Debug.Print j & ") " & myNode.Attributes(0).Text & " *** " & myNode.nodeName

    Monday, November 25, 2013 7:00 PM
  • Run-time error 91, Object variable or With block variable not set.

    It's happening on this line:

    Debug.Print j & ") " & myNode.Attributes(0).Text & " *** " & myNode.nodeName

    Try it just like this

    Debug.Print j & ") " & myNode.nodeName

    I have had problems using the Attribute property -- sometimes it works, most of the time -- not.  The nodName property I think is more important -- for identifying where you are in your data -- hierarchically.


    Rich P

    Monday, November 25, 2013 11:35 PM