PDA

View Full Version : Group by XML



WizzardOfOz
2014-09-15, 07:38 AM
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?



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

WizzardOfOz
2014-09-15, 09:13 AM
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