SharePoint Online: Export List Data to XML using PowerShell
Requirement: Export SharePoint Online List data to XML file.
PowerShell to Export SharePoint Online List to XML
Exporting a SharePoint Online list to an XML file using PowerShell can be a useful way to make a backup of the list or to transfer the list data to another system. In this article, we will walk through the steps to export a SharePoint Online list to an XML file using PowerShell.
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
#Set parameter values
$SiteURL="https://Crescent.sharepoint.com/"
$ListName="Projects"
$XMLFile ="C:\Temp\ProjectData.xml"
Try{
#Get Credentials to connect
$Cred= Get-Credential
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
#Get the web & List objects
$Web = $Ctx.Web
$Ctx.Load($Ctx.Web)
$List = $Web.Lists.GetByTitle($ListName)
$ListFields =$List.Fields
$Ctx.Load($ListFields)
$ListItems = $List.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
#Create new XML File
[System.XML.XMLDocument]$XMLDocument=New-Object System.XML.XMLDocument
#Add XML Declaration
$Declaration = $XMLDocument.CreateXmlDeclaration("1.0","UTF-8",$null)
$XMLDocument.AppendChild($Declaration)
#Create Root Node
[System.XML.XMLElement]$XMLRoot=$XMLDocument.CreateElement($ListName)
#Iterate through each List Item in the List
Foreach ($Item in $ListItems)
{
#Add child node "Item"
$ItemElement = $XMLDocument.CreateElement("Item")
$ItemElement.SetAttribute("ID", $Item.ID)
$XMLRoot.AppendChild($ItemElement)
#Loop through each column of the List Item
ForEach($Field in $ListFields | Where {$_.Hidden -eq $false -and $_.ReadOnlyField -eq $false -and $_.Group -ne "_Hidden"})
{
$FieldElement = $XMLDocument.CreateElement($Field.InternalName)
$FieldElement.Set_InnerText($Item[$Field.InternalName])
#Append to Root node
$ItemElement.AppendChild($FieldElement)
}
}
# Append Root Node to XML
$XMLDocument.AppendChild($XMLRoot)
#Save XML File
$XMLDocument.Save($XMLFile)
Write-Host -f Green "List Items Exported to XML!"
}
Catch {
write-host -f Red "Error Exporting List Data to XML!" $_.Exception.Message
}
XML file generated through PowerShell script:
This PowerShell script extracts data from all columns and exports it to XML. You may have to handle columns like People Picker, Lookup, etc., separately.
#Loop through each columns of the List Item
ForEach($Field in $ListFields | Where {$_.Hidden -eq $false -and $_.ReadOnlyField -eq $false -and $_.Group -ne "_Hidden"})
{
$FieldElement = $XMLDocument.CreateElement($Field.InternalName)
$FieldValue = $Item[$Field.InternalName]
#Check for User Field
If($Field.GetType().Name -eq "FieldUser")
{
#Get the People picker Column value of the list item
$UserFieldValue = [Microsoft.SharePoint.Client.FieldUserValue]$Item[$Field.InternalName]
#Get the Email of the User
$FieldValue = $UserFieldValue.Email #$FieldValue.LookupValue
}
$FieldElement.Set_InnerText($FieldValue)
#Append to Root node
$ItemElement.AppendChild($FieldElement)
}
Refer: How to Export SharePoint Online List Items to CSV using PowerShell?
Last but not least:
You can export the SharePoint Online list to XML using the RPC method or REST Calls
- https://YourDomain.sharepoint.com/_vti_bin/owssvr.dll?Cmd=Display&List={GUID}&XMLDATA=TRUE ,E.g. https://Crescent.sharepoint.com/_vti_bin/owssvr.dll?Cmd=Display&List={0d4ff78d-8759-46b4-94bd-d5ee5f7564a8}&XMLDATA=TRUE .To get the Complete List Structure, use: https://Crescent.sharepoint.com/_vti_bin/owssvr.dll?Cmd=ExportList&List={0d4ff78d-8759-46b4-94bd-d5ee5f7564a8}
- Use REST API Call to Get SharePoint List Data into XML format: https://Your-Domain.sharepoint.com/_api/lists/getbytitle(‘List-Name’)/items? , E.g. https://Crescent.sharepoint.com/_api/lists/getbytitle(‘Projects’)/items?
If I use RPC method to export SharePoint Online list to XML, will i be able to save it in a location on my local computer using a powershell script?
Yes! Exactly that’s what this script does. Set $XMLFile =”C:\TempProjectData.xml” according to your requirement.