[RESOLVED] Extracting specific values from XML Results

I have received data from a SOAP call and put it into an MSXML2.DOMDocument using the loadXML function. The data returned looks something like this:

Code:

---

<ResultSets>
        <ResultSet>
                <RowCount>1</RowCount>
                        <Rows>
                                <Row>
                                        <Columns>
                                                <Column>
                                                        <Value>George Davis</Value>
                                                        <Name>Employee</Name>
                                                </Column>
                                                <Column>
                                                        <Value>3/7/2019 10:04:52 AM</Value>
                                                        <Name>Begin_Time</Name>
                                                </Column>
                                                <Column>
                                                        <Value>3829927</Value>
                                                        <Name>Clockin_Key</Name>
                                                </Column>
                                                <Column>
                                                        <Value>Production</Value>
                                                        <Name>Cost_Sub_Type</Name>
                                                </Column>
                                                <Column>
                                                        <Value>cst.GeorgeD</Value>
                                                        <Name>User_ID</Name>
                                                </Column>
                                                <Column>
                                                        <Value>Davis</Value>
                                                        <Name>Last_Name</Name>
                                                </Column>
                                                <Column>
                                                        <Value>0</Value>
                                                        <Name>Contract_Worker</Name>
                                                </Column>
                                        </Columns>
                                </Row>
                        </Rows>
        </ResultSet>
</ResultSets>

---

I would like to look for specific values in the data returned. For example, just get the value of User_ID.

Is there a way, with the DOMDocument to easily find these specific values?

Thanks in advance for your help.

Check out inline comments for some XPath Queries 101 crash course

thinBasic Code:

Option Explicit
 
Private Const STR_SOAP_RESPONSE1 As String = _
"<ResultSets>" & vbCrLf & _
"    <ResultSet>" & vbCrLf & _
"        <RowCount>1</RowCount>" & vbCrLf & _
"            <Rows>" & vbCrLf & _
"                <Row>" & vbCrLf & _
"                    <Columns>" & vbCrLf & _
"                        <Column>" & vbCrLf & _
"                            <Value>George Davis</Value>" & vbCrLf & _
"                            <Name>Employee</Name>" & vbCrLf & _
"                        </Column>" & vbCrLf & _
"                        <Column>" & vbCrLf & _
"                            <Value>3/7/2019 10:04:52 AM</Value>" & vbCrLf & _
"                            <Name>Begin_Time</Name>" & vbCrLf & _
"                        </Column>" & vbCrLf & _
"                        <Column>" & vbCrLf & _
"                            <Value>3829927</Value>" & vbCrLf & _
"                            <Name>Clockin_Key</Name>" & vbCrLf & _
"                        </Column>" & vbCrLf & _
"                        <Column>" & vbCrLf
Private Const STR_SOAP_RESPONSE2 As String = _
"                            <Value>Production</Value>" & vbCrLf & _
"                            <Name>Cost_Sub_Type</Name>" & vbCrLf & _
"                        </Column>" & vbCrLf & _
"                        <Column>" & vbCrLf & _
"                            <Value>cst.GeorgeD</Value>" & vbCrLf & _
"                            <Name>User_ID</Name>" & vbCrLf & _
"                        </Column>" & vbCrLf & _
"                        <Column>" & vbCrLf & _
"                            <Value>Davis</Value>" & vbCrLf & _
"                            <Name>Last_Name</Name>" & vbCrLf & _
"                        </Column>" & vbCrLf & _
"                        <Column>" & vbCrLf & _
"                            <Value>0</Value>" & vbCrLf & _
"                            <Name>Contract_Worker</Name>" & vbCrLf & _
"                        </Column>" & vbCrLf & _
"                    </Columns>" & vbCrLf & _
"                </Row>" & vbCrLf & _
"            </Rows>" & vbCrLf & _
"    </ResultSet>" & vbCrLf & _
"</ResultSets>"
 
Private Sub Form_Load()
    Dim oDoc            As Object
    Dim oNode           As Object
    
    With CreateObject("MSXML2.DOMDocument")
        .LoadXml STR_SOAP_RESPONSE1 & STR_SOAP_RESPONSE2
        Set oDoc = .documentElement
    End With
    
    '--- first <Column>
    Set oNode = oDoc.selectSingleNode("/ResultSets/ResultSet/Rows/Row/Columns/Column")
    Debug.Print oNode.Xml, Timer
    '--- the same
    Set oNode = oDoc.selectSingleNode("//Column")
    Debug.Print oNode.Xml, Timer
    '--- the <Column> with User_ID in <Name> child
    Set oNode = oDoc.selectSingleNode("//Column[Name='User_ID']")
    Debug.Print oNode.Xml, Timer
    '--- the <Value> under <Column> with 'User_ID' in <Name> child
    Set oNode = oDoc.selectSingleNode("//Column[Name='User_ID']/Value")
    Debug.Print oNode.Xml, Timer
    '--- only <Value>'s text
    Debug.Print oNode.Text, Timer
End Sub

cheers,
</wqw>

So far I'm able to get data using the following code. However, it seems a like there would be an easier/better way to do it.

Code:

---

Sub ParseXmlDocument()
  Dim doc As New MSXML2.DOMDocument
  Dim x As Object
  Dim NodeIndex As Integer
   
  Dim success As Boolean

  NodeIndex = 0
  success = doc.loadXML(txtResponse.Text)
 
  For Each x In doc.getElementsByTagName("Name")
    If x.nodeTypedValue = "User_ID" Then
        Exit For
    End If
    NodeIndex = NodeIndex + 1
  Next

  MsgBox "User located at node " & NodeIndex & " = " & doc.getElementsByTagName("Value").Item(NodeIndex).childNodes(0).nodeValue
End Sub

---

Thanks wqweto. I'll try this.

Exactly the information I was looking for! Thank you again.

Quote:

---

Originally Posted by dlscott56

For example, just get the value of User_ID.

Is there a way, with the DOMDocument to easily find these specific values?

---

Aside from the several XML-DOM-Object-Helpers -
there's of course always the VB6-String-Functions (for simple formats like the one you've posted):

Code:

---

Option Explicit

Private Sub Form_Load()
  Const XML$ = "...<Column><Value>cst.GeorgeD</Value><Name>User_ID</Name></Column>..."
  Debug.Print GetPrecedingNodeValue(XML, "User_ID")
End Sub

Function GetPrecedingNodeValue(XML$, ValueName$, Optional PrecTag$ = "<Value>")
  Dim P As Long
      P = InStr(1, XML, ">" & ValueName & "</", 1)
  If P Then P = InStrRev(XML, PrecTag, P, 1)
  If P Then GetPrecedingNodeValue = Mid$(XML, P + Len(PrecTag), InStr(P, XML, "</") - P - Len(PrecTag))
End Function

---

Olaf

Thank you Olaf.

You can also do this using brute force techniques if your XML is simple and regular enough. If nothing ever changes this might be adequate, but in the real world even a small format change may require reworking finicky code.

Code:

---

Option Explicit

'This code assumes a lot of things, such as:
'
'  o The first row contains every field that any row contains.  it uses
'    that to create an ADO Recordset.
'
'  o The Name and Value nodes don't contain any whitespece to be
'    trimmed.
'

Private Sub Form_Load()
    Dim F As Integer
    Dim XML As String
    Dim XMLRows() As String
    Dim I As Long
    Dim XMLColumns() As String
    Dim J As Long
    Dim Names() As Variant
    Dim Values() As Variant
    Dim Records As ADODB.Recordset

    'For simplicity, just grab the sample XML from a file:
    F = FreeFile(0)
    Open "xml.txt" For Input As #F
    XML = Input$(LOF(F), #F)
    Close #F

    'Extract the body of <Rows/> by brute force:
    XML = Split(Split(XML, _
                      "<Rows>", _
                      2, _
                      vbBinaryCompare)(1), _
                "</Rows>", _
                2, _
                vbBinaryCompare)(0)

    'Extract the bodies of each <Row/>:
    XMLRows = Split(XML, "</Row>", , vbBinaryCompare)
    XML = vbNullString 'Discard, no longer needed.
    ReDim Preserve XMLRows(UBound(XMLRows) - 1)
    For I = 0 To UBound(XMLRows)
        XMLRows(I) = Split(XMLRows(I), "<Row>", 2, vbBinaryCompare)(1)
    Next

    'Extract into a Recordset.

    'Extract the bodies of each <Column/>:
    For I = 0 To UBound(XMLRows)
        XMLColumns = Split(XMLRows(I), "</Column>", , vbBinaryCompare)
        ReDim Preserve XMLColumns(UBound(XMLColumns) - 1)
        ReDim Preserve Names(UBound(XMLColumns))
        ReDim Preserve Values(UBound(XMLColumns))
        For J = 0 To UBound(XMLColumns)
            XMLColumns(J) = Split(XMLColumns(J), "<Column>", 2, vbBinaryCompare)(1)

            'Extract each <Name/> and <Value/>:
            Names(J) = Split(Split(XMLColumns(J), _
                                  "<Name>", _
                                  2, _
                                  vbBinaryCompare)(1), _
                            "</Name>", _
                            2, _
                            vbBinaryCompare)(0)
            Values(J) = Split(Split(XMLColumns(J), _
                                    "<Value>", _
                                    2, _
                                    vbBinaryCompare)(1), _
                              "</Value>", _
                              2, _
                              vbBinaryCompare)(0)
        Next
        Erase XMLColumns 'No longer needed.

        If I = 0 Then
            'Create the Recordset:
            Set Records = New ADODB.Recordset
            With Records
                With .Fields
                    For J = 0 To UBound(Names)
                        .Append Names(J), adVarWChar, 255
                    Next
                End With
                .Open
            End With
        End If

        'Add the data for this Row:
        Records.AddNew Names, Values
    Next
    Erase Values, Names, XMLColumns, XMLRows 'No longer needed.

    Set MSHFlexGrid1.DataSource = Records
    Records.Close
End Sub

Private Sub Form_Resize()
    If WindowState <> vbMinimized Then
        MSHFlexGrid1.Move 0, 0, ScaleWidth, ScaleHeight
    End If
End Sub

---

Attachment 166393

Here I added a second Row to the XML sample for generality.

Some "traps for young players" include issues like varying whitespace even within tags and "entity encoded" characters. You also have other potential issues to cope with such as attributes, CDATA sections, and more.

So I don't really advocate manually parsing XML. MSXML has both SAX2 and the DOM which wraps SAX2 parsing, so there isn't much excuse for rolling your own.

Thanks dilettante. I appreciate it.