Group by XML

WizzardOfOz

New member
Joined
Sep 4, 2013
Messages
184
Reaction score
0
Points
0
Location
Australia
Excel Version(s)
Office 365
Is it possible to use a "Group By" statement when parsing XML data.

My XML file looks like
<report>
<Category>
<Codes>
<Code>123</Code>
<Description>A category</Description>
</Codes></Category>
[Other information]
</report>

I want to return all the unique codes and descriptions.
Apart from iterating each child node and testing if this is a new code is there an easier way?

Code:
Function GroupBy(ByVal sXML As String, ByVal L1 As String, L2 As String, L3 As String) As MSXML2.IXMLDOMNode
    Dim xmldoc As MSXML2.DOMDocument60
    Dim xmlNodeList As MSXML2.IXMLDOMNode, Level1  As MSXML2.IXMLDOMNode
    Dim sCode As String
    
    Set xmldoc = New MSXML2.DOMDocument60
    xmldoc.async = False
    xmldoc.Load (sXML)
    
    Set xmlNodeList = xmldoc.createElement(L1)                  'Create level 1
    For Each Level1 In xmldoc.SelectNodes("//" & L2)            'Look for level 2
        sCode = Level1.SelectSingleNode("//" & L3).Text         'Look for level 3
        
        If xmlNodeList.SelectNodes("//" & L2 & "[" & L3 & "='" & sCode & "']").Length = 0 Then      'does it exist already?
            xmlNodeList.appendChild Level1                      'no add it
            'Debug.Print vbTab, Level1.Text
        End If
        
    Next
    
    Set GroupBy = xmlNodeList
    Set xmldoc = Nothing
    
End Function
 
Last edited:

WizzardOfOz

New member
Joined
Sep 4, 2013
Messages
184
Reaction score
0
Points
0
Location
Australia
Excel Version(s)
Office 365
Minor error in previous code, not all records returned. See Fix below


sCode = Level1.SelectSingleNode("//" & L3).Text 'Look for level 3

change this to
sCode = Level1.SelectSingleNode(L3).Text 'Look for level 3
 
Last edited:
Top