1

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?

Chang Park
  • 47
  • 1
  • 1
  • 6
  • Quick and dirty way, save the xml file (if its in some string, you can use VBA to save it). Then load the XML in workbook by `workbook.open`. Now you can see the data in tabular format. Use VBA/Formulas to find your data. – cyboashu Jun 02 '15 at 16:15

1 Answers1

1

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 :

har07
  • 88,338
  • 12
  • 84
  • 137
  • Thanks. And for any future readers, these links will work in conjunction with this answer. http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops http://stackoverflow.com/questions/5188999/query-and-parse-xml-attribute-value-into-xls-using-vba – Chang Park Jul 14 '15 at 20:57
  • Additional question: What if the query had a combination of ands and ors? For example, I want all elements with toasters that have either brand of black and decker or toolset (not listed in the example above)? – Chang Park Jul 20 '15 at 15:05
  • Nevermind, I figured it out. In order to add ANDs and ORs, use parentheses. – Chang Park Jul 20 '15 at 15:13