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?
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: