CODEWITHSUNDEEP
xmlvbaloopsms-accessnodes
stepterr
stepterr

Reputation: 21

VBA loop through XML nodes and get values within each node

In Access I have a form that will load an XML file provided by the user and then loop through to pull information from in the file. Here's the structure of the XML file.

<manifest identifier="MANIFEST1">
  <organizations />
  <resources>  
    <resource identifier="AMS_EDDC7208" type="imsqti_xmlv1p2p1" href="a1231.xml"> 
      <metadata> 
        <schema>abc123</schema>
        <schemaversion>1.2</schemaversion>
        <metadata> 
          <exmetadata>
            <exmetadataSection identifier="841" name="Topic">
              <description>
              </description>
              <exmetadataparameterSet>
                <param name="Topic" type="String">Math</param>
                <param name="Gender" type="String">N</param>
                <param name="Race" type="String">Z</param>
                <param name="Difficulty Level" type="String">HARD</param>
              </exmetadataparameterSet>
              <exdependencysection identifierref="" /> 
            </exmetadataSection>
            <exmetadataSection identifier="842" name="Math Reasoning">
              <description>
              </description>
              <exmetadataparameterSet>
                <param name="Item_Type" type="String">A</param>
                <param name="Cont1" type="String">ARI</param>
                <param name="Cont2" type="String">REM</param>
                <param name="Cognitive" type="String">PUR</param>
              </exmetadataparameterSet>
              <exdependencysection identifierref="" />
            </exmetadataSection>
          </exmetadata>
        </metadata>
      </metadata>
      <file href="a1231.xml" />
    </resource>
  <resources>  
    <resource identifier="AMS_AB3dC7208" type="imsqti_xmlv1p2p1" href="a1232.xml"> 
      <metadata> 
        <schema>abc123</schema>
        <schemaversion>1.2</schemaversion>
        <metadata> 
          <exmetadata>
            <exmetadataSection identifier="841" name="Topic">
              <description>
              </description>
              <exmetadataparameterSet>
                <param name="Topic" type="String">Math</param>
                <param name="Gender" type="String">N</param>
                <param name="Race" type="String">Z</param>
                <param name="Difficulty Level" type="String">HARD</param>
              </exmetadataparameterSet>
              <exdependencysection identifierref="" /> 
            </exmetadataSection>
            <exmetadataSection identifier="842" name="Math Reasoning">
              <description>
              </description>
              <exmetadataparameterSet>
                <param name="Item_Type" type="String">A</param>
                <param name="Cont1" type="String">GEO</param>
                <param name="Cont2" type="String">ABS</param>
                <param name="Cognitive" type="String">PUR</param>
              </exmetadataparameterSet>
              <exdependencysection identifierref="" />
            </exmetadataSection>
          </exmetadata>
        </metadata>
      </metadata>
      <file href="a1232.xml" />
    </resource>  
  <resources>  
    <resource identifier="AMS_EE0023DC7208" type="imsqti_xmlv1p2p1" href="a1233.xml"> 
      <metadata> 
        <schema>abc123</schema>
        <schemaversion>1.2</schemaversion>
      </metadata>
      <file href="a1233.xml" />
    </resource>  
  <resources>  
    <resource identifier="AMS_XXZDF2323CC208" type="imsqti_xmlv1p2p1" href="a1234.xml"> 
      <metadata> 
        <schema>abc123</schema>
        <schemaversion>1.2</schemaversion>
        <metadata> 
          <exmetadata>
            <exmetadataSection identifier="841" name="Topic">
              <description>
              </description>
              <exmetadataparameterSet>
                <param name="Topic" type="String">Math</param>
                <param name="Gender" type="String">N</param>
                <param name="Race" type="String">Z</param>
                <param name="Difficulty Level" type="String">HARD</param>
              </exmetadataparameterSet>
              <exdependencysection identifierref="" /> 
            </exmetadataSection>
            <exmetadataSection identifier="842" name="Math Reasoning">
              <description>
              </description>
              <exmetadataparameterSet>
                <param name="Item_Type" type="String">A</param>
                <param name="Cont1" type="String">ALG</param>
                <param name="Cont2" type="String">APP</param>
                <param name="Cognitive" type="String">REA</param>
              </exmetadataparameterSet>
              <exdependencysection identifierref="" />
            </exmetadataSection>
          </exmetadata>
        </metadata>
      </metadata>
      <file href="a1234.xml" />
    </resource>          
</manifest>

I'm able to loop through the resource nodes and get the identifer and href text for each node but I'm having trouble getting the correct details from the param nodes when I'm looping through each of the resource nodes. I only need certain param values so was attempting to use SelectSingleNode but realize that is only going to give me the first set it encounters.

Dim XDoc, nodesThatMatter  As Object
Dim strFieldName, strESID, strFile, strTopic, strItemType, strCont1, strCont2, strCognitive As String
Set XDoc = CreateObject("MSXML2.DOMDocument")
XDoc.async = False: XDoc.validateOnParse = False
XDoc.Load (Me.FileIn)

Set nodesThatMatter = XDoc.SelectNodes("//resource")
   
For Each node In nodesThatMatter
    
    'resource identifier
    strESID = node.Attributes.Item(0).Text
    'resource file
    strFile = node.Attributes.Item(2).Text
            
    strTopic = node.SelectSingleNode("//param[@name='Topic']").Text
    strItemType = node.SelectSingleNode("//param[@name='Item_Type']").Text
    strCont1 = node.SelectSingleNode("//param[@name='Cont1']").Text
    strCont2 = node.SelectSingleNode("//param[@name='Cont2']").Text
    strCognitive = node.SelectSingleNode("//param[@name='Cognitive']").Text
              
         
Next node
 
Set XDoc = Nothing

How can I update those values depending on the resource node I'm in as well as account for when a node may not have param nodes?

Upvotes: 2

Views: 502

Answers (1)

stepterr
stepterr

Reputation: 21

I ended up drilling down into the childnodes and was able to get to what I needed. Still wondering if there is a more direct way to do this by going directly to a specific level by the name?

Upvotes: 0

Related Questions