CODEWITHSUNDEEP
rxmlsharepoint
Dale Kube
Dale Kube

Reputation: 1460

R - Parsing XML from SharePoint List

I am collecting data from a SharePoint list. The connection works; however, the list is limited to 1,000 items when there are more than 25,000 items in the full list. The same connection with Tableau and Excel provides the full list. 

URL <- "http://XXXXXX/XXXXXX/_vti_bin/ListData.svc/RequiredLearningStatus"
URL_parsed <- xmlParse(readLines(URL,warn=F))
items <- getNodeSet(URL_parsed, "//m:properties")
x <- xmlToDataFrame(items, stringsAsFactors = FALSE)

I receive the following error message when the readLines() function executes:

"In readLines(URL): incomplete final line found"

How can I deal with the EOL error and retrieve the full list?

Upvotes: 2

Views: 1218

Answers (1)

Gaffi
Gaffi

Reputation: 4367

I only have a basic understanding here, but I will try to assist, since there are no answers as of yet, and I have been successful myself.

The structure of the URL I use for pulling in my SP list (includes all items) uses the GUID of the list, like so:

"https://XXXX/XXXX/_vti_bin/owssvr.dll?Cmd=Display&Query=*&XMLDATA=TRUE&List={<<YOUR GUID HERE>>}"

To get your GUID, I've plagiarized from this link:

There are times when you need to find the Id (a Guid) of a list – for example, when setting the Task list to be used with SharePoint Designer Workflows (see my blog post here). Here’s a simple way of doing this:

  • Navigate to the SharePoint list using the browser.
  • Select the Settings + List Settings menu command.

  • Copy the Url from the browser address bar into Notepad. It will look something like:

    • http://moss2007/ProjectX/_layouts/listedit.aspx?List=%7B26534EF9%2DAB3A%2D46E0%2DAE56%2DEFF168BE562F%7D

  • Delete everying before and including “List=”.

  • Change “%7B” to “{”
  • Change all “%2D” to “-“
  • Change “%7D” to “}”

You are now left with the Id:

{26534EF9-AB3A-46E0-AE56-EFF168BE562F}

Once you've got that URL, I am reading in the data in a slightly different fashion:

library(RCurl)
library(XML)
library(data.table)

URL <- "https://XXXX/XXXX/_vti_bin/owssvr.dll?Cmd=Display&Query=*&XMLDATA=TRUE&List={<<YOUR GUID HERE>>}"
xml <- xmlParse(getURL(URL, userpwd='<<youruser>>:<<yourpass>>'))
finalData <- data.table(do.call(rbind,xmlToList(xmlRoot(xml)[['data']]))

In the code above, be sure to replace <<YOUR GUID HERE>> and <<youruser>>:<<yourpass>> with appropriate values for your environment. There should be no < or > in any of the code (aside from R's assignment, <-).

Upvotes: 1

Related Questions