Results 1 to 2 of 2

Thread: Group by XML

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Excel Version
    Office 365

    Group by XML

    Is it possible to use a "Group By" statement when parsing XML data.

    My XML file looks like
    <Description>A category</Description>
    [Other information]

    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
        Set GroupBy = xmlNodeList
        Set xmldoc = Nothing
    End Function
    Last edited by WizzardOfOz; 2014-09-15 at 06:41 AM. Reason: error in sample XML file

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts