Convert XML to specific JSON structure with powershell



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.


No comments:

Post a Comment