Census Geocoder JSON output convert to Xml dataset using JSON.net in C#

Question

I am creating a .Net app in Visual Studio 2012 that queries an address table in my SQL dB and uses the Census Geocoding API to return the specific MSA for each address. I have existing code for the dB query, but I am having trouble with converting the Json output of the Census API to an Xml dataset. I am using Json.net to serialize the json output and then deserialize to .net in order to load into an XmlDocument. Unfortunately, I keep getting an XmlException error:

Data at the root level is invalid. Line 1, position 1

Details:

System.Xml.XmlException was unhandled HResult=-2146232000
Message=Data at the root level is invalid. Line 1, position 1.
Source=System.Xml LineNumber=1 LinePosition=1 SourceUri=""
StackTrace: at System.Xml.XmlTextReaderImpl.Throw(Exception e) at System.Xml.XmlTextReaderImpl.Throw(String res, String arg) at System.Xml.XmlTextReaderImpl.ParseRootLevelWhitespace() at System.Xml.XmlTextReaderImpl.ParseDocumentContent() at System.Xml.XmlTextReaderImpl.Read() at System.Xml.XmlLoader.Load(XmlDocument doc, XmlReader reader, Boolean preserveWhitespace) at System.Xml.XmlDocument.Load(XmlReader reader) at System.Xml.XmlDocument.LoadXml(String xml) at ConsoleApplication1.Program.Main(String[] args) in c:\Users\jdsmith\Documents\Visual Studio 2012\Projects\C#\MSA_Application_v2\MSA_Application_v2\Model\Program.cs:line 54 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() InnerException:

I believe either the Json or the Xml need to be formatted further, but I don't know how. Also, I'm sure I am making this too difficult on myself...if there is a better way, I am all ears.

Here is the sample geolookup I am using to test:

http://geocoding.geo.census.gov/geocoder/geographies/address?street=4600+Silver+Hill+Rd&city=Suitland&state=MD&benchmark=Public_AR_Census2010&vintage=Census2010_Census2010&layers=14&format=json

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Threading.Tasks;
using System.Data;
using System.Net;
using System.IO;
using System.Xml;
using System.Runtime.Serialization.Json;
using System.Xml.Linq;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        private static string geoRT = "geographies";
        private static string geoST = "address";
        private static string geoStreet = "4600+Silver+Hill+Rd";
        private static string geoCity = "Suitland";
        private static string geoState = "MD";
        private static string geoZip = "20746";
        private static string geoBM = "Public_AR_Census2010";
        private static string geoVin = "Census2010_Census2010";
        private static string geoLayer = "all";
        private static string geoFormat = "json";
        static void Main(string[] args)
        {
            StringBuilder geoRelURI = new StringBuilder();
            geoRelURI.AppendFormat(@"{0}/{1}?street={2}&city={3}&state={4}&zip={5}&benchmark={6}&vintage={7}&layers={8}&format={9}"
                , geoRT, geoST, geoStreet, geoCity, geoState, geoZip, geoBM, geoVin, geoLayer, geoFormat);

            Uri geoBaseURI = new Uri("http://geocoding.geo.census.gov/geocoder/");
            Uri geoURI = new Uri(geoBaseURI, geoRelURI.ToString());

            //Console.WriteLine(geoURI);
            //Console.ReadLine();

            WebRequest geoRequest = WebRequest.Create(geoURI);
            WebResponse geoResponse = geoRequest.GetResponse();

            Stream geoDataStream = geoResponse.GetResponseStream();
            StreamReader geoReader = new StreamReader(geoDataStream);
            string geoString = geoReader.ReadToEnd();
            var jsonConvert = JsonConvert.SerializeObject(geoString);
            string jsonString = jsonConvert.ToString();
            var xmlConvert = JsonConvert.DeserializeObject(jsonString);
            string xmlString = xmlConvert.ToString();

            XmlDocument geoXMLDoc = new XmlDocument();
            geoXMLDoc.LoadXml(xmlString);

            XmlWriterSettings xmlSettings = new XmlWriterSettings();
            xmlSettings.Indent = true;

            XmlWriter geoXMLWriter = XmlWriter.Create("geoXML.xml", xmlSettings);
            geoXMLDoc.Save(geoXMLWriter);

            Console.Write("<BR>" + geoXMLDoc.OuterXml);

            //Console.WriteLine(xmlString);
            //Console.ReadLine();

            geoDataStream.Close();
            geoResponse.Close();

        }
    }
}

dbc dbc · Accepted Answer · 2015-05-07T19:35:20

First of all, you are passing a JSON string to geoXMLDoc.LoadXml(). That's not going to work. What you want to do is to convert the JSON to an XmlDocument via JsonConvert.DeserializeXmlNode.

However, some of your JSON properties contain characters that are invalid for use in XML names, in specific whitespace:

{"Census Blocks":[{"BLKGRP":"1",

It seems that this causes DeserializeXmlNode to throw an exception. Thus you'll need to rename the names:

        var obj = JObject.Parse(geoString);
        foreach (var fix in (from property in obj.Descendants().OfType<JProperty>()
                             let newName = XmlConvert.EncodeLocalName(property.Name.Replace(" ", ""))
                             where newName != property.Name
                             select new { Old = property, New = new JProperty(newName, property.Value) })
                   .ToList())
        {
            fix.Old.Replace(fix.New);
        }

        var xmldoc = JsonConvert.DeserializeXmlNode(obj.ToString());

Census Geocoder JSON output convert to Xml dataset using JSON.net in C#

2 Answers