Extract text from XML file (multiple lines)

[erikgrave1]

Hi,
I´m struggling with extracting data from an XML file. All I need is the "Status" of each SID listed in XML file, where can be up to 50 different SIDs. This is the example of XML file.
I have solved extracting 1 status (function is down below), but it is really very slow (1 SID = 1 second, and I need to check around 50.000 in total). So my function will assign correct Status according the SID (S12345678 will be "in operation", S23456789 will be "out of operation" etc.) I think there should be used "For each ..." somehow.

Thank you very much. Your help is really appreciated. And even if there will be no solution for me, the code below will probable help someone

This is the XML file (I have added space after the <) :

  [TABLE]
<tbody>[TR]
[TD]< ?xml   version="1.0" encoding="UTF-8"?>[/TD]
[/TR]
[TR]
[TD]-< root>[/TD]
[/TR]
[TR]
[TD]-< record   type="tsacinv/system">[/TD]
[/TR]
[TR]
[TD]< status>in operation<   /status>[/TD]
[/TR]
[TR]
[TD]< systemid>S12345678<   /systemid>[/TD]
[/TR]
[TR]
[TD]< /record>[/TD]
[/TR]
[TR]
[TD]-< record   type="tsacinv/system">[/TD]
[/TR]
[TR]
[TD]< status>out of operation<   /status>[/TD]
[/TR]
[TR]
[TD]< systemid>S23456789<   /systemid>[/TD]
[/TR]
[TR]
[TD]< /record>[/TD]
[/TR]
[TR]
[TD]-< record   type="tsacinv/system">[/TD]
[/TR]
[TR]
[TD]< status>in operation<   /status>[/TD]
[/TR]
[TR]
[TD]< systemid>S34567890<   /systemid>[/TD]
[/TR]
[TR]
[TD]< /record>[/TD]
[/TR]
[TR]
[TD]< /root>[/TD]
[/TR]
</tbody>[/TABLE]

This is my function (working for 1 SID) :

Public Function statusAM(S As Range) As Variant

myURL = "" '<- here is the link for getting the XML file, using variable "S" from a cell for SID added to the link

Set xmlDoc = CreateObject("Microsoft.XMLDOM")
xmlDoc.SetProperty "SelectionLanguage", "XPath"
xmlDoc.async = False

With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", myURL, False
    .Send
    xmlDoc.LoadXML .responseText
End With

Set NodeXML = xmlDoc.getElementsByTagName("status")
statusAM = NodeXML(0).text

End Function

 

[John_w]

Here are 3 methods:

    Dim recordNodes As Object 'IXMLDOMNodeList
    Dim recordNode As Object 'IXMLDOMNode
    
    Set recordNodes = XMLdoc.SelectNodes("root/record")
    For Each recordNode In recordNodes
        Debug.Print recordNode.SelectSingleNode("systemid").Text; " ";
        Debug.Print recordNode.SelectSingleNode("status").Text
    Next
    
    Dim statusNodes As Object 'IXMLDOMNodeList
    Dim statusNode As Object 'IXMLDOMNode
    
    Set statusNodes = XMLdoc.getElementsByTagName("status")
    For Each statusNode In statusNodes
        Debug.Print statusNode.Text
    Next

    Set recordNodes = XMLdoc.SelectNodes("root/record")
    For Each recordNode In recordNodes
        Debug.Print recordNode.ChildNodes(1).nodeTypedValue; " ";
        Debug.Print recordNode.ChildNodes(0).nodeTypedValue
    Next

 

[erikgrave1]

Hello,

thank you John_w
all of them are perfect and work like a charm.
I used 3rd method and added to my script.
Is it also possible to get the ID of childNode somehow ? (in reverse direction)

getElementsByTagName("status")

-> output will be 1 (ChildNodes(1))

Many thanks

 

[John_w]

I don't know what you're asking for. By 'ID' do you mean array index? The "status" child node in the example XML is always the 0th array index in the ChildNodes array, not 1. To find the array index of each "status" try this:

    Dim i As Long
    For Each recordNode In recordNodes
        For i = 0 To recordNode.ChildNodes.Length - 1
            If recordNode.ChildNodes(i).nodeName = "status" Then
                Debug.Print i
                Exit For
            End If
        Next
    Next

The above prints 0 for all 3 records.

 

[erikgrave1]

John_w,

yes exactly this. Thank you !