1

I would like to load the contents of some spreadsheet into an XmlDocument. What I got is:

let req = WebRequest.Create "http://spreadsheets.google.com/ccc?key=pXMFQ3e4aFY11oQkT_7FhZg"
let resp = req.GetResponse ()
let stream = resp.GetResponseStream ()
let xdoc = new XmlDocument()
xdoc.Load stream

I get the following error:

System.Xml.XmlException: An error occurred while parsing EntityName. Line 12, position 22057.
  at System.Xml.XmlTextReaderImpl.Throw(Exception e)
  at System.Xml.XmlTextReaderImpl.ParseEntityName()
  at System.Xml.XmlTextReaderImpl.ParseEntityReference()
  at System.Xml.XmlTextReaderImpl.Read()
  at System.Xml.XmlLoader.LoadNode(Boolean skipOverWhitespace)
  at System.Xml.XmlLoader.LoadDocSequence(XmlDocument parentDoc)
  at System.Xml.XmlDocument.Load(XmlReader reader)
  at System.Xml.XmlDocument.Load(Stream inStream)
  at <StartupCode$FSI_0008>.$FSI_0008.main@()
Stopped due to error

Replacing that spreadsheet with the simplest one with only a column of few integers does not change anything.

Does it have something to do specifically with "Google Sheets" and if so, what should I do prior to loading or do I have to go down a different route?

Community
  • 1
  • 1
Sasha Babaei
  • 455
  • 3
  • 8
  • 1
    Where did you get that link? Does it require authentication? How do you know that it would return XML? – Fyodor Soikin May 17 '17 at 03:16
  • Do you have bare `&` in your xml at the position the exception points to? – Pawel May 17 '17 at 03:17
  • It is a data file from guardian.co.uk for UK Inflation Rates since 1948 and it is from a public data repository so no authentication. I can easily export it to XML on Google Docs itself. – Sasha Babaei May 17 '17 at 03:26
  • Repo: https://www.theguardian.com/news/datablog/2011/jan/27/data-store-office-for-national-statistics – Sasha Babaei May 17 '17 at 03:26
  • I do not see any & in the XML converted by the Google Docs Add-On. – Sasha Babaei May 17 '17 at 03:29
  • 2
    You dont get xml returned from the above link. To get an xml response you will need to modify the url like so: `https://spreadsheets.google.com/feeds/list/1KCPTCEaGSi9OEaoQVzSyEh8xzPSolWwaXd3iwSjw0sU/od6/public/basic?alt=rss` this URL points to the spreadsheet mentioned in your question. The alphanumeric in between list/ and /od6 is the spreadsheet id (not the key) – Jack Brown May 17 '17 at 04:41
  • I also used this: https://docs.google.com/spreadsheets/d/1KCPTCEaGSi9OEaoQVzSyEh8xzPSolWwaXd3iwSjw0sU/edit#gid=0 – Sasha Babaei May 17 '17 at 05:00
  • That link does not work for me. Changing `basic?alt=rss` to `values` does not work either. I get: "The remote server returned an error: (400) Bad Request." – Sasha Babaei May 17 '17 at 05:18
  • 1
    For some reason, the link in code block seems to not work as expected. Please try this link, you should be able to see the [xml response](https://spreadsheets.google.com/feeds/list/1KCPTCEaGSi9OEaoQVzSyEh8xzPSolWwaXd3iwSjw0sU/od6/public/basic?alt=rss) in the browser itself – Jack Brown May 17 '17 at 16:14
  • Worked like a charm .. thanx. What's the trick with the URL if you don't mind me asking? – Sasha Babaei May 19 '17 at 17:53

0 Answers0