Parse Multilevel XML input using Excel VBA

In general, need to parse input matching certain filter criteria from XML using Excel VBA. The complication lies in the multilevel XML attributes and its pairing result which has to be populated in sequence inside Excel.

XML content as below:

<Flow index="1" item="1" itemName="BB_150" ID="XXX">
      <Attribute name="FUNCTIONAL_X" dataType="Double" unit="" multiplier="" tag="YY">1</Attribute>
      <Attribute name="X_LOC" dataType="String" unit="" multiplier="" tag="YY">2</Attribute>
</Flow>
<Flow index="2" item="2" itemName="CC_200" ID="WWW">
      <Attribute name="FUNCTIONAL_X" dataType="Double" unit="" multiplier="" tag="YY">15</Attribute>
      <Attribute name="X_LOC" dataType="String" unit="" multiplier="" tag="YY">3</Attribute>
</Flow>
<Flow index="3" item="3" itemName="DD_200" ID="UUU">
      <Attribute name="FUNCTIONAL_X" dataType="Double" unit="" multiplier="" tag="YY">20</Attribute>
      <Attribute name="X_LOC" dataType="String" unit="" multiplier="" tag="YY">1</Attribute>
</Flow>
<Flow index="4" item="4" itemName="EE_115" ID="SSS">
      <Attribute name="FUNCTIONAL_X" dataType="Double" unit="" multiplier="" tag="YY">33</Attribute>
      <Attribute name="X_LOC" dataType="String" unit="" multiplier="" tag="YY">2</Attribute>
</Flow>

1. How do i read only those nodes starting with Attribute in the XML?
2. Once all nodes with Attribute selected, further parsing in Excel VBA starts. First, look for name=X_LOC and get the value.
3. Then, look for name = FUNCTIONAL_X and retrieve the value.
4. Now, need to put all these values into Excel column in worksheet 'Result'. The format of output into Excel as below:

Column (starts with Column A..the开发者_开发技巧n continue to next column..etc) Header Name = X_LOC (sort and display in ascending order) Row Result (starts with row1..then row2..etc) = FUNCTIONAL_X (per each pairing X_LOC)

Result populated into Excel should look like below:

A  B  C  D (Excel column)

1  2  3  4 (X_LOC value)

A1 B1 C1 D1 (Excel Row)

1  15 20 33 (FUNCTIONAL_X value)

Note: if there's repeat of same value for X_LOC, do nothing. Remain the existing X_LOC & its FUNCTIONAL_X value. No overwrite needed.

---

Manipulating XML data in Excel is unbelievably cumbersome. I would just stick to VBA. Create an XML document as shown below, edit it (using XPath to access the required nodes/elements) and then save it.

Set xmlInventory = CreateObject("Msxml2.DOMDocument.6.0")

Documentation: http://msdn.microsoft.com/en-us/library/ms756987%28v=VS.85%29.aspx

XPath tutorial: http://www.w3schools.com/xpath/default.asp