Results 1 to 2 of 2

Thread: Group by XML

  1. #1
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0

    Group by XML



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 by WizzardOfOz; 2014-09-15 at 06:41 AM. Reason: error in sample XML file

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    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 by WizzardOfOz; 2014-09-15 at 08:14 AM. Reason: Lost CRLF formatting

Posting Permissions

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