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
Re: [RESOLVED] Extracting specific values from XML Results
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
Re: [RESOLVED] Extracting specific values from XML Results
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
Here I added a second Row to the XML sample for generality.
Re: [RESOLVED] Extracting specific values from XML Results
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.
[RESOLVED] Extracting specific values from XML Results
Reply With Quote
Originally Posted by dlscott56
