long time to load XML values into the array

lesmly · September 29, 2022

I have several XML files that I need to load into the arrays. My code loads them, but the time is far too long in my opinion. It should be instant and it takes a few seconds for each file.

Example xml attached.

How to make it faster?

#include <MsgBoxConstants.au3>
#include <XML.au3>
#include <Array.au3>

$filexml = @WorkingDir & "\test02.xml"
_ReadPartXML($filexml)

Func _ReadPartXML($sMainXmlPath)
    $tTimer = TimerInit()

    $XML_FILE = $sMainXmlPath

    Local $oXmlDoc, $oNodes, $oNode
    Local $iPECount

    Enum $MPart_pid, $MPart_OAT, $MPart_ID, $MPart_pext, $MPart_sn02, $MPart_sn03, $MPart_AS, $MPart_AT, $MPart_AL, $MPart_VID, $MPart_LSL, $MPart_LPC, $MPart_LPP, _
            $MPart_LD, $MPart_LDR, $MPart_CTS, $MPart_REC, $MPart_BND, $MPart_NOC, $MPart_PEARRAY, $MPart_SCNew111, $MPart_PT, $MPart_LPCD, $MPart_SID, $MPart_FLD_COUNT
    Local $aMainParts[0][$MPart_FLD_COUNT]

    Enum $PE_n01, $PE_n02, $PE_n03, $PE_n04, $PE_n05, $PE_FLD_COUNT
    Local $aPEs[0][$PE_FLD_COUNT]
    Enum $SPL_E_ID, $UNQ_P_ID, $SEG_E_ID, $SEG_D01, $SEG_D01_F, $SEG_UPD01, $SEG_UPD02, $DEL_NR, $DEL_NR02, _
            $DEL_NR03, $DEL_NR04, $DEL_NR05, $aSCNew_COUNT
    Local $aSCNew[$aSCNew_COUNT]

    $oXmlDoc = _XML_CreateDOMDocument()
    _XML_Load($oXmlDoc, $XML_FILE)
    If @error Then Exit MsgBox($MB_ICONERROR + $MB_TOPMOST, "ERROR", "_XML_Load failed - @error = " & @error)
    $oNodes = _XML_SelectNodes($oXmlDoc, '//Allparts/Part')
    $fDiff = TimerDiff($tTimer)
    ConsoleWrite("xml load: " & $fDiff & @CR)

    For $i = 0 To $oNodes.Length - 1
        _ArrayAdd($aMainParts, "")

        $oNode = $oNodes.Item($i)
        $aMainParts[$i][$MPart_pid] = _XML_GetValue($oNode, '@id')[1]
        $aMainParts[$i][$MPart_OAT] = _XML_GetValue($oNode, 'SN01')[1]
        $aMainParts[$i][$MPart_ID] = _XML_GetValue($oNode, 'PName')[1]
        If _XML_NodeExists($oNode, 'PExt') Then
            $aMainParts[$i][$MPart_pext] = _XML_GetValue($oNode, 'PExt')[1]
        EndIf
        $aMainParts[$i][$MPart_sn02] = _XML_GetValue($oNode, 'SN01')[1]
        $aMainParts[$i][$MPart_sn03] = _XML_GetValue($oNode, 'SN03')[1]
        $aMainParts[$i][$MPart_AS] = _XML_GetValue($oNode, 'CPU/CType')[1]
        $aMainParts[$i][$MPart_AT] = _XML_GetValue($oNode, 'CPU/CBrand')[1]
        $aMainParts[$i][$MPart_AL] = _XML_GetValue($oNode, 'CPU/CExt')[1]
        $aMainParts[$i][$MPart_VID] = _XML_GetValue($oNode, 'Fan')[1]
        $aMainParts[$i][$MPart_LSL] = _XML_GetValue($oNode, 'GPU/GNum01')[1]
        $aMainParts[$i][$MPart_LPC] = _XML_GetValue($oNode, 'GPU/GNum02')[1]
        $aMainParts[$i][$MPart_LPP] = _XML_GetValue($oNode, 'GPU/GNum04')[1]
        $aMainParts[$i][$MPart_LD] = _XML_GetValue($oNode, 'GPU/GNum05')[1]
        $aMainParts[$i][$MPart_LDR] = _XML_GetValue($oNode, 'GPU/GNum06')[1]
        If _XML_NodeExists($oNode, 'CTS') Then
            $aMainParts[$i][$MPart_CTS] = _XML_GetValue($oNode, 'CTS')[1]
        EndIf
        $aMainParts[$i][$MPart_REC] = _XML_GetValue($oNode, 'PowerSN')[1]
        If _XML_NodeExists($oNode, 'BND') Then
            $aMainParts[$i][$MPart_BND] = _XML_GetValue($oNode, 'Band')[1]
        EndIf
        $aMainParts[$i][$MPart_NOC] = _XML_GetValue($oNode, 'NOPE')[1]

        $iPECount = _XML_GetValue($oNode, 'NOPE')[1]
        ReDim $aPEs[0][$PE_FLD_COUNT]
        For $j = 0 To $iPECount - 1
            _ArrayAdd($aPEs, "")
            $aPEs[$j][$PE_n01] = _XML_GetValue($oNode, 'OPE[' & $j + 1 & ']/@id')[1]
            $aPEs[$j][$PE_n02] = _XML_GetValue($oNode, 'OPE[' & $j + 1 & ']/OPESN01')[1]
            $aPEs[$j][$PE_n03] = _XML_GetValue($oNode, 'OPE[' & $j + 1 & ']/OPESN02')[1]
            $aPEs[$j][$PE_n04] = _XML_GetValue($oNode, 'OPE[' & $j + 1 & ']/OPEID')[1]
            $aPEs[$j][$PE_n05] = _XML_GetValue($oNode, 'OPE[' & $j + 1 & ']/OPEType')[1]
        Next
        $aMainParts[$i][$MPart_PEARRAY] = $aPEs

        If _XML_NodeExists($oNode, 'SCNew') Then
            $aSCNew[$SEG_E_ID] = _XML_GetValue($oNode, 'SCNew/SCList/SC03')[1]
            $aSCNew[$SEG_D01] = _XML_GetValue($oNode, 'SCNew/SCList/SC04')[1]
            $aSCNew[$SEG_D01_F] = _XML_GetValue($oNode, 'SCNew/SCList/SC05')[1]
            $aSCNew[$SEG_UPD01] = _XML_GetValue($oNode, 'SCNew/SCList/SC06')[1]
            $aSCNew[$SEG_UPD02] = _XML_GetValue($oNode, 'SCNew/SCList/SC07')[1]
            $aSCNew[$DEL_NR] = _XML_GetValue($oNode, 'SCNew/SCList/SC08')[1]
            $aSCNew[$DEL_NR02] = _XML_GetValue($oNode, 'SCNew/SCList/SC09')[1]
            $aSCNew[$DEL_NR03] = _XML_GetValue($oNode, 'SCNew/SCList/SC10')[1]
            $aSCNew[$DEL_NR04] = _XML_GetValue($oNode, 'SCNew/SCList/SC11')[1]
            $aSCNew[$DEL_NR05] = _XML_GetValue($oNode, 'SCNew/SCList/SC12')[1]
            $aMainParts[$i][$MPart_SCNew111] = $aSCNew
        EndIf

        $aMainParts[$i][$MPart_PT] = _XML_GetValue($oNode, 'PType')[1]

        If _XML_NodeExists($oNode, 'NC01') Then
            $aMainParts[$i][$MPart_SID] = _XML_GetValue($oNode, 'NC01')[1]
        EndIf
    Next
    $fDiff = TimerDiff($tTimer)
    ConsoleWrite("array load: " & $fDiff & @CR)
    Return ($aMainParts)
EndFunc   ;==>_ReadPartXML

test02.xml

abberration · September 29, 2022

I played around with your script and found that after part #238, the script seems to lock up. I didn't see anything weird about #239 and deleting it didn't fix the problem. I'm a bit too tired to dig any deeper. I only changed the line "For $i = 0 To $oNodes.Length - 1" to "For $i = 0 To 238 ; #239 locks up the script" and added an arraydisplay right before the return. Sorry I can't be any more help tonight, but I hope it helps you troubleshoot.

#include <MsgBoxConstants.au3>
#include <XML.au3>
#include <Array.au3>

$filexml = @WorkingDir & "\test02.xml"
_ReadPartXML($filexml)

Func _ReadPartXML($sMainXmlPath)
    $tTimer = TimerInit()

    $XML_FILE = $sMainXmlPath

    Local $oXmlDoc, $oNodes, $oNode
    Local $iPECount

    Enum $MPart_pid, $MPart_OAT, $MPart_ID, $MPart_pext, $MPart_sn02, $MPart_sn03, $MPart_AS, $MPart_AT, $MPart_AL, $MPart_VID, $MPart_LSL, $MPart_LPC, $MPart_LPP, _
            $MPart_LD, $MPart_LDR, $MPart_CTS, $MPart_REC, $MPart_BND, $MPart_NOC, $MPart_PEARRAY, $MPart_SCNew111, $MPart_PT, $MPart_LPCD, $MPart_SID, $MPart_FLD_COUNT
    Local $aMainParts[0][$MPart_FLD_COUNT]

    Enum $PE_n01, $PE_n02, $PE_n03, $PE_n04, $PE_n05, $PE_FLD_COUNT
    Local $aPEs[0][$PE_FLD_COUNT]
    Enum $SPL_E_ID, $UNQ_P_ID, $SEG_E_ID, $SEG_D01, $SEG_D01_F, $SEG_UPD01, $SEG_UPD02, $DEL_NR, $DEL_NR02, _
            $DEL_NR03, $DEL_NR04, $DEL_NR05, $aSCNew_COUNT
    Local $aSCNew[$aSCNew_COUNT]

    $oXmlDoc = _XML_CreateDOMDocument()
    _XML_Load($oXmlDoc, $XML_FILE)
    If @error Then Exit MsgBox($MB_ICONERROR + $MB_TOPMOST, "ERROR", "_XML_Load failed - @error = " & @error)
    $oNodes = _XML_SelectNodes($oXmlDoc, '//Allparts/Part')
    $fDiff = TimerDiff($tTimer)
    ConsoleWrite("xml load: " & $fDiff & @CR)

    For $i = 0 To 238 ; #239 locks up the script
         _ArrayAdd($aMainParts, "")

        $oNode = $oNodes.Item($i)
        $aMainParts[$i][$MPart_pid] = _XML_GetValue($oNode, '@id')[1]
        $aMainParts[$i][$MPart_OAT] = _XML_GetValue($oNode, 'SN01')[1]
        $aMainParts[$i][$MPart_ID] = _XML_GetValue($oNode, 'PName')[1]
        If _XML_NodeExists($oNode, 'PExt') Then
            $aMainParts[$i][$MPart_pext] = _XML_GetValue($oNode, 'PExt')[1]
        EndIf
        $aMainParts[$i][$MPart_sn02] = _XML_GetValue($oNode, 'SN01')[1]
        $aMainParts[$i][$MPart_sn03] = _XML_GetValue($oNode, 'SN03')[1]
        $aMainParts[$i][$MPart_AS] = _XML_GetValue($oNode, 'CPU/CType')[1]
        $aMainParts[$i][$MPart_AT] = _XML_GetValue($oNode, 'CPU/CBrand')[1]
        $aMainParts[$i][$MPart_AL] = _XML_GetValue($oNode, 'CPU/CExt')[1]
        $aMainParts[$i][$MPart_VID] = _XML_GetValue($oNode, 'Fan')[1]
        $aMainParts[$i][$MPart_LSL] = _XML_GetValue($oNode, 'GPU/GNum01')[1]
        $aMainParts[$i][$MPart_LPC] = _XML_GetValue($oNode, 'GPU/GNum02')[1]
        $aMainParts[$i][$MPart_LPP] = _XML_GetValue($oNode, 'GPU/GNum04')[1]
        $aMainParts[$i][$MPart_LD] = _XML_GetValue($oNode, 'GPU/GNum05')[1]
        $aMainParts[$i][$MPart_LDR] = _XML_GetValue($oNode, 'GPU/GNum06')[1]
        If _XML_NodeExists($oNode, 'CTS') Then
            $aMainParts[$i][$MPart_CTS] = _XML_GetValue($oNode, 'CTS')[1]
        EndIf
        $aMainParts[$i][$MPart_REC] = _XML_GetValue($oNode, 'PowerSN')[1]
        If _XML_NodeExists($oNode, 'BND') Then
            $aMainParts[$i][$MPart_BND] = _XML_GetValue($oNode, 'Band')[1]
        EndIf
        $aMainParts[$i][$MPart_NOC] = _XML_GetValue($oNode, 'NOPE')[1]

        $iPECount = _XML_GetValue($oNode, 'NOPE')[1]
        ReDim $aPEs[0][$PE_FLD_COUNT]
        For $j = 0 To $iPECount - 1
            _ArrayAdd($aPEs, "")
            $aPEs[$j][$PE_n01] = _XML_GetValue($oNode, 'OPE[' & $j + 1 & ']/@id')[1]
            $aPEs[$j][$PE_n02] = _XML_GetValue($oNode, 'OPE[' & $j + 1 & ']/OPESN01')[1]
            $aPEs[$j][$PE_n03] = _XML_GetValue($oNode, 'OPE[' & $j + 1 & ']/OPESN02')[1]
            $aPEs[$j][$PE_n04] = _XML_GetValue($oNode, 'OPE[' & $j + 1 & ']/OPEID')[1]
            $aPEs[$j][$PE_n05] = _XML_GetValue($oNode, 'OPE[' & $j + 1 & ']/OPEType')[1]
        Next
        $aMainParts[$i][$MPart_PEARRAY] = $aPEs

        If _XML_NodeExists($oNode, 'SCNew') Then
            $aSCNew[$SEG_E_ID] = _XML_GetValue($oNode, 'SCNew/SCList/SC03')[1]
            $aSCNew[$SEG_D01] = _XML_GetValue($oNode, 'SCNew/SCList/SC04')[1]
            $aSCNew[$SEG_D01_F] = _XML_GetValue($oNode, 'SCNew/SCList/SC05')[1]
            $aSCNew[$SEG_UPD01] = _XML_GetValue($oNode, 'SCNew/SCList/SC06')[1]
            $aSCNew[$SEG_UPD02] = _XML_GetValue($oNode, 'SCNew/SCList/SC07')[1]
            $aSCNew[$DEL_NR] = _XML_GetValue($oNode, 'SCNew/SCList/SC08')[1]
            $aSCNew[$DEL_NR02] = _XML_GetValue($oNode, 'SCNew/SCList/SC09')[1]
            $aSCNew[$DEL_NR03] = _XML_GetValue($oNode, 'SCNew/SCList/SC10')[1]
            $aSCNew[$DEL_NR04] = _XML_GetValue($oNode, 'SCNew/SCList/SC11')[1]
            $aSCNew[$DEL_NR05] = _XML_GetValue($oNode, 'SCNew/SCList/SC12')[1]
            $aMainParts[$i][$MPart_SCNew111] = $aSCNew
        EndIf

        $aMainParts[$i][$MPart_PT] = _XML_GetValue($oNode, 'PType')[1]

        If _XML_NodeExists($oNode, 'NC01') Then
            $aMainParts[$i][$MPart_SID] = _XML_GetValue($oNode, 'NC01')[1]
        EndIf
    Next
    $fDiff = TimerDiff($tTimer)
    ConsoleWrite("array load: " & $fDiff & @CR)
    _ArrayDisplay($aMainParts) ; Added this to show your results!
    Return ($aMainParts)
EndFunc   ;==>_ReadPartXML

lesmly · September 30, 2022

In my case, there is no lock up after 238. Although at later positions it actually loads slightly slower.

TheXman · September 30, 2022

On 9/29/2022 at 12:12 PM, lesmly said:

My code loads them, but the time is far too long in my opinion. It should be instant and it takes a few seconds for each file.

Are you able to retrieve the data in any format other than XML, like JSON, CSV, or TSV?

I'm not sure what you consider "instant", but the example below was able to convert the test02.json data to TSV data in 0.349 seconds and then convert the TSV data to an AutoIt array in an additional 0.026 seconds. That's a total elapsed time of 0.375 seconds. That elapsed time is certainly better than "a few seconds". I converted the test02.xml to test02.json using an online website (https://jsonformatter.org/xml-formatter). So the script below is an example that assumes you can either get your data as JSON or that you can/will convert your XML to JSON files before processing them. If you can get your data in CSV or TSV formats, then there are ways to convert it just as quickly as the JSON example.

For reference, the script in your original post took about 3.5 seconds to run on the PC in which I ran the example script below.

Note: For simplicity, the columns that may contain multiple values, like CPU, GPU, and SCList, were created as a list of "|"-separated values instead of arrays. You can use a function like StringSplit() or StringRegExp() to parse out individual values.

Example

#AutoIt3Wrapper_AU3Check_Parameters=-w 3 -w 4 -w 5 -w 6 -d

#include <Constants.au3>
#include <MyIncludes\jq\jq.au3>
#include <Array.au3>


example()

Func example()
    Const $JSON_FILE = "test02.json"

    Local $hTimer     = -1
    Local $sCmdOutput = ""
    Local $aResult[0][6]

    ;The 2 lines below are only needed if the jq executale is not in the PATH or @ScriptDir
    _jqInit("C:\Utils\JQ\jq-win64.exe")
    If @error Then Exit MsgBox($MB_ICONERROR + $MB_TOPMOST, "ERROR", "Unable to find jq executable - @error = " & @error)

    ;Execute jq filter: Parse result into a tab-separated variable (TSV) list
    $hTimer     = TimerInit()
    $sCmdOutput = _jqExecFile($JSON_FILE, "", '-r -f json_to_tsv.jq')
    If @error Then
        MsgBox($MB_ICONERROR + $MB_TOPMOST, "ERROR", "jqExec failed - @error = " & @error & " @extended = " & @extended)
        ConsoleWrite("ERROR:" & @CRLF & $sCmdOutput & @CRLF)
        Exit
    EndIf
    ConsoleWrite(StringFormat("Elapsed time to create TSV data:     %.3f seconds", TimerDiff($hTimer) / 1000) & @CRLF)

    ;Load TSV result into an array and display it
    _ArrayAdd($aResult, $sCmdOutput, 0, @TAB, @CRLF)
    ConsoleWrite(StringFormat("Elapsed time to create AutoIt array: %.3f seconds", TimerDiff($hTimer) / 1000) & @CRLF)
    
    _ArrayDisplay($aResult, "Parsed JSON Data", "", 0, Default, "ID|PName|PExt|CPU|GPU|SCList")
EndFunc

Output

Elapsed time to create TSV data:     0.349 seconds
Elapsed time to create AutoIt array: 0.375 seconds

Example Array

image.png.0e1492ba2d1e7609f2cafa199be81e7c.png

test02.json

Edited September 30, 2022 by TheXman

lesmly · October 1, 2022

Thank you very much for your reply.

Instant means for me - less than 1 second.

Unfortunately, I can't download the file in a format other than xml. Nor can I use online tools to convert files. But I can try to convert the xml file to another format - for now I don't know how - maybe a little hint? That online converter you mentioned - it would be great if there was such a UDF function in autoit.

TheXman · October 1, 2022

You're welcome.

Here's a little hint, if you are truly interested in learning how to do it the way I suggested, then use your favorite web search engine and search for command line tools that will convert XML to JSON. When / If you find one that you like, start the scripting cycle of "trial -> error -> understanding" and see if you can figure out a solution that works for you. Of course doing it the way I suggested will require that you also learn how to write a simple jq filter to create the a TSV list from the JSON data. If you come to a point where you absolutely cannot proceed further without help, then come back, show your best effort or closest solution, explain where or how it is coming up short, and ask for assistance in understanding how to get past whatever obstacle you are encountering. I hope that helps. 😉

For the record, I was able to find an XML to JSON command line utility that works quite well and is only about 15K in size. After adding the conversion step to the previous script, I got the following times:

Elapsed time to convert XML to JSON: 0.092 seconds
Elapsed time to create TSV data:     0.435 seconds
Elapsed time to create AutoIt array: 0.460 seconds

As you can see, even with the conversion process, your XML file can still be converted into an AutoIt array in well under 1 second by using 2 command line tools (1 to convert XML to JSON and 1 to process the JSON and create a TSV file).

argumentum · October 1, 2022

1 hour ago, TheXman said:

I was able to find an XML to JSON command line utility that works quite well and is only about 15K in size

can you share a link ?

TheXman · October 1, 2022

I could, but I was trying to gauge @lesmly's interest by seeing some effort. As it says in my profile's About Me, I'm "Always willing to help those who are engaged in active learning, not passive receipt of information.". So if/when I see no effort to learn, I know not to waste my time.

Edited October 1, 2022 by TheXman

lesmly · October 1, 2022

Finding a command line utility was one of my first ideas. But I found either for Linux, or uncompiled code, or paid or requiring specific installation of additional programs. And also
https : //github.com/javadev/underscore-java/releases/download/test/xmltojson.exe
this is a while ago, at the moment I don't know how it should work. The only working idea was Saxon XSLT but it works on my laptop, and on the target computer where I need to run the program from a flash drive - there is no java.
How did you find this tool so quickly? I guess it's a mischief of fate (it's in my language, I don't know if the translation is understandable) Please provide a link - it won't be the end of the job for me. I'm treating it as a learning experience as I don't really need to do it, the code can run - long operating times are not a problem. I want to learn, so I ask for help. But to start the "trial -> error -> understanding" method - I need this xml to json converter. Without that, I won't start.

edit: tool xmlojson.exe mentioned above works quite good (xmltojson.exe -s test02.xml -d abc.json), but I can test your tool too

Edited October 1, 2022 by lesmly
new information

TheXman · October 1, 2022

https://www.bjelic.net/2012/08/01/coding/convert-xml-to-json-using-xslt/

23 minutes ago, lesmly said:

it won't be the end of the job for me. I'm treating it as a learning experience as I don't really need to do it, the code can run - long operating times are not a problem.

Since "it won't be the end of the job" for you and "you really don't need to do it", I guess I'll leave you to figure out the rest on your own or with someone else's help.

:bye:

lesmly · October 2, 2022

If I offended with something, I apologize - maybe it's translation or maybe Asperger's spectrum (mine :)).
Thank you so much for your help, now I just want to go further on my own - without wasting your time. And this is possible thanks to you!
Thank you and I hope that if I encounter problems I will get your help in the future too!

TheXman · October 2, 2022

On 10/1/2022 at 4:18 AM, lesmly said:

it would be great if there was such a UDF function in autoit

I created and posted one earlier today. Enjoy!

Edited October 2, 2022 by TheXman

long time to load XML values into the array

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members