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
XML file generated through PowerShell script:
This PowerShell script extracts data from all columns and exports to XML. You may have to handle columns like People Picker, Lookup, etc separately.
Last but not least:
You can export SharePoint Online list to XML using RPC method or REST Calls
PowerShell to Export SharePoint Online List to XML
#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://crescenttech.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 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) }
Last but not least:
You can export SharePoint Online list to XML using RPC method or REST Calls
- https://YourDomain.sharepoint.com/_vti_bin/owssvr.dll?Cmd=Display&List={GUID}&XMLDATA=TRUE ,E.g. https://crescenttech.sharepoint.com/_vti_bin/owssvr.dll?Cmd=Display&List={0d4ff78d-8759-46b4-94bd-d5ee5f7564a8}&XMLDATA=TRUE .To get the Complete List Structure, use: https://crescenttech.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://crescenttech.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?
ReplyDeleteYes! Exactly that's what this script does. Set $XMLFile ="C:\Temp\ProjectData.xml" according to your requirement.
Delete