Export SharePoint list data to XML directly from the GUI

The other day I learned of a cool function in SharePoint which can come in handy if you want to export a list to XML. And best of all, no code, script or SharePoint Destroyer… *cough* … Designer needed. What you do is simply to call an OOTB SharePoint service and specify in the query string what it is you want, and in which format:

http://<site url>/_vti_bin/owssvr.dll?Cmd=Display&List=<list guid>&View=<view guid>&Query=*&XMLDATA=TRUE

So what you do is to call the owssvr.dll from the site you want to export from, and in the query string add Cmd=Display. Then you add the List and View you want to export from. If you want all items and fields you simply set Query=*. Mind, you still might have to reference a view, even though it won’t be used when using the query. And in the end, add XMLDATA=TRUE. That’s it! An example of how it might look:

http://myawesomesite/_vti_bin/owssvr.dll?Cmd=Display&List={002A6DE2-7638-4FEF-A7CD-7427D4DECABA}&View={757d5548-eafc-4a5f-8ef4-e0be36d790a3}&Query=*&XMLDATA=TRUE

You can get the guid to the list by simply going to the list settings and copy the guid from the url. Its the guid after “…&List=”. That’s it. =) Some documentation about it and other SharePoint services: http://msdn.microsoft.com/en-us/library/dd588689(v=office.11).aspx

Advertisements

19 thoughts on “Export SharePoint list data to XML directly from the GUI”

  1. Haha this is awesome. I think ive come across this service sometime long ago as well. Good post! Im just curious. What client demands are the underlying cause for this solution?

    1. Hi Johan, and thanks! =)

      The reason for using this service in our case was simply our own preference. We’re going to migrate data, and prefer to have it in an XML file rather than an Excel file. Of course we could write our own export, but MS has been nice enough to give us this, so we don’t have to. I think the strength of this is that you can export from a production environment without having to run scripts or deploy custom code. That means you only need permissions to use integration services and read the list, same as with Export to Excel.

  2. Hi Johannes,

    I’m trying to get data from SharePoint List view to an MS Access 2010 db for some analisys.

    Here is what I did:
    I created links from ShP lists to Access db.
    I switched on Caching which enables Offline mode.
    This worked..until the list was extended up to 128 fields.

    Now MS Access interface, if Cashing is used, cannot deal with it, so I’m trying to solve this other way (Office Update which solve this issue doesn’t work, neither change in Registry).

    I also tried to import List View data (which has less fields) to Access thru VBA, but that doesn’t work too (https://msdn.microsoft.com/en-us/library/bb238962(v=office.12).aspx).

    So now I’m concentrated to getting XML file from a list view, and linking it to a Access.
    But, it is important that XML file is linked to a SharePoint list view, so the data is up to date.

    I must admit I’m not VBA expert, so if you can give me some directions how to to call an OOTB SharePoint service in VBA, I would appreciate it.

    regards,

    Vedran

    1. Hi Vedran,

      I have not used Access a lot, and cannot give you much advice there. But your problem seem to stem from having an excess to 128 fields. In my opinion, if you have a list with 128 fields, something is seriously wrong with the list design (data model). I would focus on reducing the amount of fields rather than trying to fix the issues in Access. Even if you get it working, working with the Access file would be a hassle for users.

      And I strongly advice NOT to use VBA macros unless you absolutely have to. They add an often unneccessary level of complexity, and you can never be sure that they will run since they execute on the client, and the client can turn off macros, or they can fail without the error being logged.

      Now if you still HAVE to write the macro, I’m sorry to say that I’m not experienced enough to help you with it. I avoid writing them, and have only done so a handful of times. And never used them for getting data in this way. =(

      Since I don’t know what the list or Access db is for, I don’t know what other viable options there might be. But if you just want the SP list data available in a file which can be edited outside of SP, you can use the Export to Excel function. That gives you a live connection to the SP list. Not sure if it handles 128+ fields though.

  3. You mention how to get the guid for the particular list you want to export to xml but you do not provide how to get the guid for the view. Looks like you need a guid for a list and a guid for a view. Perhaps im misunderstanding but seems that part is missing. Thanks for instructions on how to do this if I can find the guid for my view then im good to go.

    1. Hi,

      My bad. I think you can do it the same way as getting the list guid. Just go to the list and open the view you want, then copy the guid after “&view=”.

      I’m on parental leave atm and can’t confirm it though. If I remember I’ll update my post later. Thanks for pointing it out. =)

  4. Hi,

    Thanks for this. I just have one problem. When I do this its only adding some of the items to the XML file. For this instance only 199 out of 1479 of the items are exported. Any suggestions? Thanks.

    1. Hmm. The first thing that pops into mind is permissions. Do you have access to all 1479 items? Otherwise I can’t really think of anything except for it being a bug. There may be something in your list which the service can’t handle. Sorry, but other than that, I don’t have any idea.

  5. Great Solution Johannes, its exactly what I was looking for .. do you have any suggestion/ideas on how I would automate the download of the xml file. I need to get this file downloaded and ftp it daily ?

    1. You should be able to use anything that can send a http request I think. For example write a PowerShell script and then run it as a Scheduled Task on the ftp server. I bet there are much better, cleaner solutions to do it, but that’s the first thing that pops into mind. How to run a PS-script as a scheduled task is just a google search away. =)

  6. Johan, thanks for the quick reply, crazy thing is that I’ve spend the better part of the day trying to get the powershell function to work, I tied to follow your Comment section on the script but no dice. Im not real good with PS and added my URL to where I thought it should go – any chance you can make it dummy proof for me by showing me exactly where I need to change the URL and List GUID etc. ?

    Thanks !

    function Export-OIPSPListDataAsXml {

    [CmdLetBinding()]
    param(
    [Parameter(Mandatory=$true)]
    [Microsoft.SharePoint.PowerShell.SPWebPipeBind]$Web,
    [Parameter(Mandatory=$true)]
    [string]$ListName,
    [Parameter(Mandatory=$true)]
    [string]$ViewName)

    $spWeb = $Web.Read()
    $list = $spWeb.Lists[$ListName]
    if (!$list) { throw "List $ListName on web $($spWeb.Url) could not be found" }
    $view = $list.Views[$ViewName]
    if (!$view) { throw "View $ViewName on web $ListName could not be found" }
    $url = “https://xxxxxxx.com/_vti_bin/owssvr.dll?Cmd=Display&List={BDD984F4-E2B3-4701-BC56-4CE8C9EFFF4B}&XMLDATA=TRUE”
    -f $spWeb.Url, $list.ID, $view.ID
    $wc = New-Object Net.WebClient
    $wc.UseDefaultCredentials = $true
    $wc.DownloadString($url)
    }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s