SharePoint Online: Import XML to List using PowerShell
Requirement: Import XML to SharePoint Online List.
PowerShell to Import XML to SharePoint Online List
Importing data from an XML file to a SharePoint Online list using PowerShell can be a useful way to migrate data from another system or to update existing list data. This article describes how to import an XML file into a SharePoint Online list using PowerShell.
Here is the PowerShell to import XML to the SharePoint Online list:
#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)
$Ctx.ExecuteQuery()
#import xml file
[xml]$ProjectXmlFile = Get-Content $XMLFile
#Iterate through each "Project" node of the XML file
Foreach ($XMLProject in $ProjectXmlFile.projects.project)
{
#Add New List Item
$ListItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
$NewProject = $List.AddItem($ListItemInfo)
#Map XML fields to SharePoint Online List Fields - Internal Name
$NewProject["ProjectID"] = $XMLProject.id
$NewProject["Title"] = $XMLProject.ProjectName
$NewProject["ProjectDescription"] = $XMLProject.description
$NewProject["ProjectManager"] = $web.EnsureUser($XMLProject.manager)
$NewProject["Domain"] = $XMLProject.domain
$NewProject["Budget"] = $XMLProject.budget
$NewProject["StartDate"] = $XMLProject.startdate
$NewProject.Update()
Write-Host "Project $($XMLProject.id) has been Imported to the Projects List!"
}
$Ctx.ExecuteQuery()
}
Catch {
write-host -f Red "Error Importing XML Data into List!" $_.Exception.Message
}
My XML Structure:
<?xml version="1.0" encoding="UTF-8"?>
<projects>
<project id="PMO-1250">
<projectname>SharePoint 2016 Upgrade</projectname>
<description>Migration Project to Move from SharePoint 2010 to SharePoint 2016</description>
<manager>[email protected]</manager>
<budget>$75000</budget>
<domain>IT Applications</domain>
<startdate>1/1/2016</startdate>
</project>
<project id="PMO-1121">
<projectname>Azure Data center Migration</projectname>
<description>Azure Data center Migration Project</description>
<manager>[email protected]</manager>
<budget>$120000</budget>
<domain>IT Infrastructure</domain>
<startdate>2/1/2017</startdate>
</project>
</projects>
Here is the List structure I created before running the script:
Here is another post was written for SharePoint On-premises: PowerShell to Import XML to SharePoint List
