Quick Links

Other stuff

Site owners

  • Bruce Mcpherson

Services‎ > ‎Desktop Liberation‎ > ‎jSon‎ > ‎

XML to JSON with VBA

In Delegating xml to json conversion to GAS I showed how you could get Google Apps Script to convert XML to JSON by posting the XML and getting back the JSON response - using VBA to illustrate. 

Here's a native VBA version. Again we'll be using cjobject (see  How to use cJobject) to hold the JSON representation of an XML object. In the example given here, we'll do a query to an API and automatically detect if it is JSON or XML. If it's XML, we'll convert it to JSON. In either case - a cJobject is the result. 

The test

Like in Delegating xml to json conversion to GAS, first off we'll use the open weather API. One thing i noticed is that this API the XML format returns a different dataset than the JSON format - strange but true.

Here's all we need. I'm stringifying the returned object to JSON to be able to print the result.

Private Sub testGetAuto()
    Dim url As String
    url = _
     "http://api.openweathermap.org/data/2.5/weather?q=London&mode=xml"
    Debug.Print getAndMakeJobjectAuto(url).stringify(True)
End Sub

Although this returns XML (as shown in Delegating xml to json conversion to GAS), getAndMakeJobjectAuto() will convert it as required, giving this result. It's not so good as the real JSON result, since all attributes are considered to be strings in XML, but perfectly usable, and actually better than the Google Apps Script version, since we dont have those trailing Text elements observed in Delegating xml to json conversion to GAS

{
    "version": "1.0",
    "encoding": "utf-8",
    "current": {
        "city": {
            "id": "2643743",
            "name": "London",
            "coord": {
                "lon": "-0.12574",
                "lat": "51.50853"
            },
            "country": "GB",
            "sun": {
                "rise": "2013-10-30T06:51:47",
                "set": "2013-10-30T16:36:28"
            }
        },
        "temperature": {
            "value": "281.629",
            "min": "281.629",
            "max": "281.629",
            "unit": "kelvin"
        },
        "humidity": {
            "value": "86",
            "unit": "%"
        },
        "pressure": {
            "value": "1027.09",
            "unit": "hPa"
        },
        "wind": {
            "speed": {
                "value": "2.4",
                "name": "Light breeze"
            },
            "direction": {
                "value": "209.5",
                "code": "SSW",
                "name": "South-southwest"
            }
        },
        "clouds": {
            "value": "0",
            "name": "sky is clear"
        },
        "precipitation": {
            "mode": "no"
        },
        "weather": {
            "number": "800",
            "value": "Sky is Clear",
            "icon": "01d"
        },
        "lastupdate": {
            "value": "2013-10-30T10:02:45"
        }
    }
}

This time we'll call the JSON version of the API
Private Sub testGetAuto()
    Dim url As String
    url = _
     "http://api.openweathermap.org/data/2.5/weather?q=London&mode=json"
    Debug.Print getAndMakeJobjectAuto(url).stringify(True)
End Sub


which gives us this
{
    "coord": {
        "lon": -0.12574,
        "lat": 51.50853
    },
    "sys": {
        "country": "GB",
        "sunrise": 1383115907,
        "sunset": 1383150988
    },
    "weather": [
        {
            "id": 800,
            "main": "Clear",
            "description": "Sky is Clear",
            "icon": "01d"
        }
    ],
    "base": "gdps stations",
    "main": {
        "temp": 281.629,
        "temp_min": 281.629,
        "temp_max": 281.629,
        "pressure": 1027.09,
        "sea_level": 1035.98,
        "grnd_level": 1027.09,
        "humidity": 86
    },
    "wind": {
        "speed": 2.4,
        "deg": 209.5
    },
    "rain": {
        "3h": 0
    },
    "clouds": {
        "all": 0
    },
    "dt": 1383128466,
    "id": 2643743,
    "name": "London",
    "cod": 200
}

Handling Arrays

In JSON an array is clearly identified [..]. In XML, not so much. Consider this
<names>
  <name>
    <first>john</first>
    <last>smith</last>
  </name>
  <name>
    <first>mary</first>
     <last>jones</last>
  </name>
</names>

It's intuitively obvious that names is an array of name objects. The simple rule is that the converter will assume this is an array if child object element node names repeat. So the above example gets converted to 
{
    "names": [
        {
            "name": {
                "first": "john",
                "last": "smith"
            }
        },
        {
            "name": {
                "first": "mary",
                "last": "jones"
            }
        }
    ]
}


The Code


Here's the code for getting the data and converting it as necessary

Here's the parser.

You can get me on Google plus, Twitter or this forum.

For help and more information join our forum,follow the blog or follow me on twitter .


Comments