Converting XML to JSON with Powershell

Question

I am trying to convert an xml file to json for further processing. This is actually first time I use powershell so I need some help.

I get an xml file with this structure:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="https://www.something..."?>
<tabela>
    <naslov>Robno knjigovodstvo - pregled automatskog stanja zaliha artikala</naslov>
    <dataset>qu1RobaZalihe</dataset>
    <datum_kreiranja>12.02.2021</datum_kreiranja>
    <zaglavlje><sifra>Šifra</sifra><naziv>Naziv</naziv><katbroj>Kataloški broj</katbroj><cfJedmj>JedMj</cfJedmj><stanje>Stanje</stanje><cijena>Nabavna cijena</cijena><datum>Datum dokumenta</datum></zaglavlje>
    <redovi>
            <red>
            <sifra>1</sifra>
            <naziv>CORE HIT</naziv>
            <katbroj>A4B0000006606</katbroj>
            <cfJedmj>KOM</cfJedmj>
            <stanje>1</stanje>
            <cijena>100,00</cijena>
            <datum>01.01.2021</datum>
        </red>
        <red>
            <sifra>2</sifra>
            <naziv>CORE HIT</naziv>
            <katbroj>A4B0000008340</katbroj>
            <cfJedmj>KOM</cfJedmj>
            <stanje>8</stanje>
            <cijena>100,00</cijena>
            <datum>01.01.2021</datum>
        </red>
        ...
    </redovi>
</tabela>

I need to ger an array of objets separated with comma like this:

    [
    {
        "sifra":  "1",
        "naziv":  "CORE HIT 7050",
        "jed_mj":  "KOM",
        "kolicina":  "1",
        "skladiste":  "1",
        "pn":  "TNX:A4B0000006606"
    },
    {
        "sifra":  "2",
        "naziv":  "CORE HIT 7020",
        "jed_mj":  "KOM",
        "kolicina":  "8",
        "skladiste":  "1",
        "pn":  "TNX:A4B0000008340"
    },
    {
        "sifra":  "3",
        "naziv":  "SYSTEM SW LICENCE AND CD-ROM",
        "jed_mj":  "KOM",
        "kolicina":  "1",
        "skladiste":  "1",
        "pn":  "TNX:A4B0000007760"
    },
    ...
]

... but instead I get only objects like this:

    {
    "sifra":  "1",
    "naziv":  "CORE HIT 7050",
    "pn":  "TNX:A4B0000006606",
    "jed_mj":  "KOM",
    "kolicina":  "1",
    "skladiste":  "1"
}
{
    "sifra":  "2",
    "naziv":  "CORE HIT 7020",
    "pn":  "TNX:A4B0000008340",
    "jed_mj":  "KOM",
    "kolicina":  "8",
    "skladiste":  "1"
}
{
    "sifra":  "3",
    "naziv":  "SYSTEM SW LICENCE AND CD-ROM",
    "pn":  "TNX:A4B0000007760",
    "jed_mj":  "KOM",
    "kolicina":  "1",
    "skladiste":  "1"
}

So far I managed to figure out some things but not all :) Code I use to get this is following:

[xml]$xml = Get-Content xml-skladiste.XML

foreach ($atr in $xml.tabela.redovi.red)
{
        $prop = [ordered]@{
        'sifra'    = $atr.sifra
        'naziv'    = $atr.naziv
        'pn'    = $atr.katbroj
        'jed_mj'    = $atr.cfJedmj
        'kolicina'    = $atr.stanje
        'skladiste' = "1"
    }
    New-Object -Type PSCustomObject -Property $prop | ConvertTo-Json | Add-Content -Path "output.json"
}

What I have to add in order to get desirable structure?

zett42 · Accepted Answer · 2021-02-17T08:23:06.760

You are currently creating multiple JSON documents, one for each element of the array $xml.tabela.redovi.red, joined together in a single file.

Move ConvertTo-Json out of the loop to fix the problem. This way you separate the creation of the data from conversion to JSON so ConvertTo-JSON can operate on the complete data structure.

[xml]$xml = Get-Content xml-skladiste.XML

$array = foreach ($atr in $xml.tabela.redovi.red)
{
    $prop = [ordered]@{
        'sifra'    = $atr.sifra
        'naziv'    = $atr.naziv
        'pn'    = $atr.katbroj
        'jed_mj'    = $atr.cfJedmj
        'kolicina'    = $atr.stanje
        'skladiste' = "1"
    }
    New-Object -Type PSCustomObject -Property $prop
}

$array | ConvertTo-Json | Set-Content -Path "output.json"

Explanation:

$array = foreach ... captures the output of the foreach loop in the $array variable, automatically creating an array.
New-Object ... produces the output that is captured. Note that we didn't have to use Write-Output because of PowerShell's implicit output behaviour (read more about it).
$array | ConvertTo-Json ... passes the whole array to a single invocation of ConvertTo-Json and finally writes it to the output file.

Thank you very much for the solution AND moreover for the EXPLANATION. This works like a charm. — Alvsky, Feb 17 '21 at 08:50

Converting XML to JSON with Powershell

1 Answers1