CODEWITHSUNDEEP
xmlvba
Chang Park
Chang Park

Reputation: 57

VBA and XML - Search trhough XML doc to locate a specific node by it's value

How can I search through an XML structure to find a specific node, using VBA, based on the node name and value? In other words, is it possible to perform a SQL-like command on the XML structure to find what I'm looking for?

For example, lets say that I have this XML structure: 

<House>
<Kitchen>
    <Appliance>
        <Name>Refrigerator</Name>
        <Brand>Maytag</Brand>
        <Model>F2039-39</Model>
    </Appliance>
    <Appliance>
        <Name>Toaster</Name>
        <Brand>Black and Decker</Brand>
        <Model>B8d-k30</Model>
    </Appliance>
</Kitchen>

I would like a way to find all toasters, that are made by black and decker, with a model of B8d-k30 using VBA. Is this possible?

Upvotes: 1

Views: 1631

Answers (1)

har07
har07

Reputation: 89295

There is a query language for XML that is supported by VBA, namely XPath.

"I would like a way to find all toasters, that are made by black and decker, with a model of B8d-k30"

In xpath, that can be translated to the following :

//Appliance[Name='Toaster' and Brand='Black and Decker' and Model='B8d-k30']

brief explanation about the xpath above :

  • //Appliance : find all <Appliance> elements, anywhere in the XML document
  • [] : filter current context element (<Appliance> in this particular case) with the following criteria....
  • Name='Toaster' : has, at least, one child element <Name> having value equals "Toaster"
  • and Brand='Black and Decker' : and has, at least, one child element <Brand> having value equals "Black and Decker"
  • and Model='B8d-k30' : and has, at least, one child element <Model> having value equals "B8d-k30"

I'm not familiar with VBA, so no VBA code sample from me. Anyway, if you search the internet there are plenty of exmples on how to execute xpath query in VBA, one example that I found is in this link.

for further reference :

Upvotes: 1

Related Questions