Convert XML to specific JSON structure with powershell

Question

Need a help on converting an xml to a specific json structure. XML would look like this,

<DataGrid> 
<DataRow>
    <RowID>1</RowID>
    <Date>26/10/2014</Date>
    <Owner>ABC Company</Owner>        
    <Make>Mitsubishi</Make>
    <Model_Family>Lancer</Model_Family>
    <Submodel>Lancer 2.0 GSR Hatch CVT</Submodel>
    <Origin/>
    <CC_Rating>1997</CC_Rating>
    <Weight>2000</Weight> 
</DataRow> 
<DataRow>
    <RowID>2</RowID>
    <Date>26/10/2014</Date>
    <Owner>ABC Company</Owner>        
    <Make>Mazda</Make>
    <Model_Family>Axela</Model_Family>
    <Submodel/>
    <Origin>Japan</Origin>
    <CC_Rating>1497</CC_Rating>
    <Weight/> 
</DataRow>
 <DataRow>
    <RowID>3</RowID>
    <Date>26/10/2014</Date>
    <Owner>Test Company</Owner>        
    <Make>Kia</Make>
    <Model_Family>Sorento</Model_Family>
    <Submodel/>
    <Origin>Korea</Origin>
    <CC_Rating>2200</CC_Rating>
    <Weight>2500<Weight> 
</DataRow>
<DataRow>
    <RowID>4</RowID>
    <Date>26/10/2014</Date>
    <Owner>Test Company</Owner>        
    <Make>Nissan</Make>
    <Model_Family>Pathfinder</Model_Family>
    <Submodel>SUV<Submodel>
    <Origin>Japan</Origin>
    <CC_Rating>2000</CC_Rating>
    <Weight>2000<Weight> 
</DataRow>

There can be one or more files in above format, my requirement is to read all those files and group them by Owner and call a REST web service by converting those object to JSON. Required JSON format will be as follows.

{
"batches": [
    {
        "Owner": "ABC Company",
        "Date": "2014-10-26",
        "Vehicles": [
            {                    
                "Make": "Mitsubishi",
                "ModelFamily": "Lancer",
                "SubModel": "Lancer 2.0 GSR Hatch CVT",
                "Origin": null
                "CcRating": "1997",
                "Weight": "2000"                    
            },
            {                   
                "Make": "Mazda",
                "ModelFamily": "Axela",
                "SubModel": null,
                "Origin": "Japan",
                "CcRating": "1497",
                "Weight": null                   
            }
        ]
    },
    {
        "Owner": "Test Company",
        "Date": "2014-10-26",
        "Vehicles": [
            {                   
                "Make": "Kia",
                "ModelFamily": "Sorento",
                "SubModel": null,
                "Origin": "Korea",
                "CcRating": "2200",
                "Weight": "2500"                  
            },
            {                    
                "Make": "Nissan",
                "ModelFamily": "Pathfinder",
                "SubModel": "SUV",
                "Origin": "Japan",
                "CcRating": "2000",
                "Weight": "2000"                   
            }
        ]
    }
]

}

This need to be done using windows powershell, Iam a java guy and have no idea how to do it using powershell except calling remote ftp server and read all xml files. Really appreciate, if someone could help me this.

Answer 1

XML Stuff

Powershell has some stuff for working with XML. First, it has a native [XML] datatype. You can cast strings to XML like so:

$someXml = '...' # pretend there's XML in there!
$xmlObject = [XML]$someXml

The ConvertTo-Xml cmdlet takes a string or other object and converts it to XML, either as a Document (XML object), a string, or a stream (array of strings). You can pass to it a string, or an object:

Reading From a File:

$xmlObject = [XML](Get-Content -Path C:\Path\to\my.xml)
$xmlObject = Get-Content -Path C:\Path\to\my.xml | ConvertTo-Xml

Working with XML Objects

Once you've got your object, you can access nodes as properties:

$xmlObject.SomeNode.SomeChild

You can also use XPATH to select a single node or multiple nodes:

$xmlObject.SelectSingleNode("//this[1]")
$xmlObject.SelectNodes("//these")

Or to do it in a more powershell way you might use the Select-Xml cmdlet:

$xmlObject | Select-Xml "//these"

I'm leaving out a lot of other stuff, especially manipulation, because it seems like you just need to find information and group it together.

JSON Stuff

There isn't a lot to know about JSON in powershell.

Use ConvertFrom-JSON to convert existing JSON into an object, and use ConvertTo-JSON to convert an object into a JSON string.

Hashtables

Sometimes called hashes or associate arrays, as I'm sure you're aware. In Powershell you use them like this:

$hash = @{
    Key1 = 'Value1'
    Key2 = 'Value2'
    Key3 = 10
}

# on one line:
$hash = @{ Key1='Val1';Key2='Val2' }

# adding pairs
$hash['NewKey'] = 'NewVal'
$hash.NewKey = 'NewVal'

# retrieving
$hash['NewKey']
$hash.NewKey

The value can be an array, an object, another hash, etc.

$complex = @{
    ThisThing = @{
        Key1 = 'val1'
        Key2 = 5
    } 
}

REST

Invoke-RestMethod is the easiest way to make REST calls in Powershell (requires version 3.0+).

How to Proceed

Once you're able to parse the information out of the XML, build a nested hash or array of hashes that contains the structure you want, and then convert it to JSON:

$mySpecialHash | ConvertTo-JSON

Take special note of how arrays and hashes are represented in the resulting JSON and maybe change the way you're building them to get the output you want.

If you have specific questions about a particular method or piece of code, then post specific questions on SO about that piece of it.

References

XML

• A very detailed and useful page on working with XML in Powershell
• ConvertTo-Xml
• Select-Xml

JSON

• ConvertTo-JSON

REST

• Invoke-RestMethod