Convert xml or json to an array.

[GZE]

Hey,

Is there an (easy) way to convert a xml or json file to a 2D array?
I have been looking arround the forum but, by lack of srcipting talent, failed to find a solution

The output array should look like this (it will be more collomns with the real data set):

Id          Name
550       Name01
537       Name02
519       Name03
...

The data to be converted comes from a rest api respons and can be xlm or json...

<UserStories>
  <UserStory Id="550" Name="name01" />
  <UserStory Id="537" Name="name02" />
  <UserStory Id="519" Name="name03" />
  <UserStory Id="516" Name="name04" />
  <UserStory Id="514" Name="name05" />
  <UserStory Id="512" Name="name06" />
  <UserStory Id="509" Name="name07" />
  <UserStory Id="469" Name="name08" />
  <UserStory Id="468" Name="name09" />
  <UserStory Id="442" Name="name10" />
 </UserStories>

expandcollapse popup

{
  "Items": [
    {
      "Id": 537,
      "Name": "Name01"
    },
    {
      "Id": 519,
      "Name": "Name02"
    },
    {
      "Id": 516,
      "Name": "Name03"
    },
    {
      "Id": 514,
      "Name": "Name03"
    },
    {
      "Id": 512,
      "Name": "Name04"
    },
    {
      "Id": 509,
      "Name": "Name05"
    },
    {
      "Id": 469,
      "Name": "Name06"
    },
    {
      "Id": 468,
      "Name": "Name07"
    },
    {
      "Id": 442,
      "Name": "Name08"
    },
    {
      "Id": 440,
      "Name": "Name09"
    },
    {
      "Id": 433,
      "Name": "Name10"
    },
  ]
}

Thanks in advance,

Simon

[MikahS]

Have a look at the >JSON UDF library.

[mikell]

Assuming that an 'Id' contains digits only and 'Name' is never empty, you can try something like this (which works in both cases)

#Include <Array.au3>

$txt = FileRead("1.txt")  ; your xml or json file
$res = StringRegExp($txt, '\WId\W+(\d+)\W+Name\W+([^"]+)', 3)
Local $array[UBound($res)/2][2]
For $i = 0 to UBound($res)-1
   If Mod($i, 2) = 0 Then
       $array[$i/2][0] = $res[$i]
   Else
       $array[($i-1)/2][1] = $res[$i]
EndIf
Next
_ArrayDisplay($array)

Edited October 8, 2014 by mikell

[jdelaney]

XMLDom:

#include <Array.au3>

$sXML = '<UserStories>' & _
  '<UserStory Id="550" Name="name01" />' & _
  '<UserStory Id="537" Name="name02" />' & _
  '<UserStory Id="519" Name="name03" />' & _
  '<UserStory Id="516" Name="name04" />' & _
  '<UserStory Id="514" Name="name05" />' & _
  '<UserStory Id="512" Name="name06" />' & _
  '<UserStory Id="509" Name="name07" />' & _
  '<UserStory Id="469" Name="name08" />' & _
  '<UserStory Id="468" Name="name09" />' & _
  '<UserStory Id="442" Name="name10" />' & _
 '</UserStories>'
$oXML = ObjCreate("Microsoft.XMLDOM")
$oXML.loadxml($sXML)

$oStories = $oXML.SelectNodes('//UserStory')
Local $array
For $oStory In $oStories
    $id = $oStory.GetAttribute("Id")
    $name = $oStory.GetAttribute("Name")
    If IsArray($array) Then
        ReDim $array[UBound($array)+1][2]
        $array[UBound($array)-1][0] = $id
        $array[UBound($array)-1][1] = $name
    Else
        Local $array[1][2]
        $array[UBound($array)-1][0] = $id
        $array[UBound($array)-1][1] = $name
    EndIf
Next
_ArrayDisplay($array)

Probably better to do what you need within the loop, but you asked for an array, so...

[mLipok]

GZE

open this WIKI page:

https://www.autoitscript.com/wiki/User_Defined_Functions

and try to find XML and JSON

mLipok

[Shane0000]

'?do=embed' frameborder='0' data-embedContent>>

I have been using this one , i haven't checked out the UDF above

[GZE]

Hello,

Thanks for the assistance...
It amazes me how responsive this forum is!!!

I used the code from jdelaney as primary inspiration and altered it to be independent from the number of columns.

Looking at the code now it looks quit simple but it was, for me, quit a struggle to get the syntax right. As many of you already noticed my scripting skills basically end(ed) after arrays.

This is the code i ended up with:

expandcollapse popup

#include <Array.au3>

$sXML = '<UserStories>' & _
  '<UserStory Id="550" Name="name01" />' & _
  '<UserStory Id="537" Name="name02" />' & _
  '<UserStory Id="519" Name="name03" />' & _
  '<UserStory Id="516" Name="name04" />' & _
  '<UserStory Id="514" Name="name05" />' & _
  '<UserStory Id="512" Name="name06" />' & _
  '<UserStory Id="509" Name="name07" />' & _
  '<UserStory Id="469" Name="name08" />' & _
  '<UserStory Id="468" Name="name09" />' & _
  '<UserStory Id="442" Name="name10" />' & _
  '</UserStories>'


_ArrayDisplay(XmlToArray($sXML))

Func XmlToArray($sXML)
    
    local $resultArray[0][0]

    ; Create XML OBJECT
    $oXML = ObjCreate("Microsoft.XMLDOM")
    $oXML.loadxml($sXML)
    $oRecords = $oXML.documentElement.childNodes

    ; Create Array with headers in row 0
    $length = $oRecords.item(0).attributes.length
    ReDim $resultArray[1][$length]
    $j = 0
    For $name in $oRecords.item(0).attributes
        $name = $oRecords.item(0).attributes($j).name
        $resultArray[0][$j] = $name
        $j+=1
    Next

    ; Fill array with values
    For $oRecord In $oRecords
        ReDim $resultArray[UBound($resultArray, $UBOUND_ROWS)+1][UBound($resultArray, $UBOUND_COLUMNS)]
        For $j = 0 To UBound($resultArray, $UBOUND_COLUMNS) - 1 Step +1
            $attributeName = $resultArray[0][$j]
            $attributeValue = $oRecord.GetAttribute($attributeName)
            $resultArray[UBound($resultArray)-1][$j] = $attributeValue
        Next
    Next
    Return $resultArray
EndFunc

FYI...
My little scripting project is to create some reporting (eg cumulative flow diagram, throughput,..) from data pulled (rest api) from an online Kanban board.
Until now i had to export csv files manually from the site (the csv''s where off course read into arrays); this function will eliminate this manual step (not yet the arrays ).

Can SciTe autocomplete XML DOM syntax?

Thanks again for all the responses,

Simon

 

Edited October 11, 2014 by GZE