Selecting an xml node from it's tag attribute rather than "tagname" using VBA

Question

Sample XML:

<DATA>   
<DIMENSION Name="NST1">
    <MEMBERS>
        <MEMBER>
            <LABEL>[None]</LABEL>
            <AT Name="IsCalculated">N</AT>
            <AT Name="SwitchSignForFlow">N</AT>
            <AT Name="SwitchTypeForFlow">N</AT>
        </MEMBER>
        <MEMBER>
            <LABEL>TotalNST1</LABEL>
            <AT Name="IsCalculated">N</AT>
            <AT Name="SwitchSignForFlow">N</AT>
            <AT Name="SwitchTypeForFlow">N</AT>
        </MEMBER>
    </MEMBERS>
</DIMENSION>
<DIMENSION Name="NST2">
    <MEMBERS>
        <MEMBER>
            <LABEL>[None]</LABEL>
            <AT Name="IsCalculated">N</AT>
            <AT Name="SwitchSignForFlow">N</AT>
            <AT Name="SwitchTypeForFlow">N</AT>
        </MEMBER>
        <MEMBER>
            <LABEL>TotalNST1</LABEL>
            <AT Name="IsCalculated">N</AT>
            <AT Name="SwitchSignForFlow">N</AT>
            <AT Name="SwitchTypeForFlow">N</AT>
        </MEMBER>
    </MEMBERS>
</DIMENSION>
 |
 |    MANY MANY MORE DIMENSIONS HERE
 |
</DATA>

Using the above xml file i am trying to use VBA to create a dictionary with a number of keys which correspond to the name attributes of each DIMENSION node and all the elements contained with in concatenated as the entry associated with that key.

 IE:
 Key:     Data:
 NST1:    NST1; [None]; N; N; N;  
 NST2:    NST2; [None]; N; N; N;

It is simple enough to use getElementsByTagName(DIMENSION) however my problem is that as you can see the XML file contains many dimensions with the same layout and tag name "DIMENSION" only distinguished by attribute Name in the Dimension tag.

Is there a way to select the dimension by its "name attribute" ie NST1, NST2 etc rather than it's tag name?

I have the code required to create these dictionary entries once my VBA script is "looking at the correct dimension" my issue is to "look at" a dimension by its attribute?

Any thoughts or ideas would be gratefully received.

Many thanks

See this SO post: stackoverflow.com/questions/14728102/…. More specifically, the code snippet that follows "First, i get all "element"-nodes that have a name-attribute: " — Cindy Meister
– Cindy Meister, Commented Feb 22, 2016 at 16:23
Thanks for this link, it got me most of the way their, I think i just needed to brush up on how xpath is used. — VBA_kid
– VBA_kid, Commented Feb 23, 2016 at 10:12

omegastripes · Accepted Answer · 2016-02-22 18:46:04Z

1

You may just use XPath like in the below example:

Set objXMLDoc = CreateObject("Msxml2.DOMDocument")
objXMLDoc.async = False
objXMLDoc.load "C:\Sample.xml"
objXMLDoc.setProperty "SelectionLanguage", "XPath"
' set target value of the Name attribute NST2
strTargetName = "NST2"
' the resulting node list contains all DIMENSION nodes having NST2 as Name
Set objNodeList = objXMLDoc.documentElement.selectNodes("//DIMENSION[@Name='" & strTargetName & "']")
' show the content of the first NST2 node from collection
MsgBox objNodeList(0).xml

I saved your XML snippet into C:\Sample.xml, the output is as follows:

<DIMENSION Name="NST2">
    <MEMBERS>
        <MEMBER>
            <LABEL>[None]</LABEL>
            <AT Name="IsCalculated">N</AT>
            <AT Name="SwitchSignForFlow">N</AT>
            <AT Name="SwitchTypeForFlow">N</AT>
        </MEMBER>
        <MEMBER>
            <LABEL>TotalNST1</LABEL>
            <AT Name="IsCalculated">N</AT>
            <AT Name="SwitchSignForFlow">N</AT>
            <AT Name="SwitchTypeForFlow">N</AT>
        </MEMBER>
    </MEMBERS>
</DIMENSION>

answered Feb 22, 2016 at 18:46

omegastripes

12.6k4 gold badges52 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

VBA_kid Over a year ago

Thank you between this and the comment above I got there, the problem was mostly my lack of understanding of Xpath.... The more you know

Collectives™ on Stack Overflow

Selecting an xml node from it's tag attribute rather than "tagname" using VBA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related