Import XML File Data into SharePoint List using PowerShell
Requirement: PowerShell to Import XML to SharePoint List
We have a utility from a third-party system that generates project data in XML format. We had to import those data to a list in the PMO site. In other words: We’ve to import from XML file to SharePoint list.
Here is a sample XML file, generated by the tool:
<?xml version="1.0"?>
<projects>
<project id="PMO.1120">
<description>GIS upgrade 2013 </description>
<manager>global\E372440</manager>
<cost>$35000</cost>
<startdate>1/1/2014</startdate>
</project>
<project id="PMO.1121">
<description>HRIT Asset Life Cycle Automation</description>
<manager>AMER\E132321</manager>
<cost>$63000</cost>
<startdate>1/1/2014</startdate>
</project>
</projects>
Using PowerShell, lets import XML data into SharePoint list.
PowerShell script to read from XML and import to SharePoint list:
Assuming we’ve an existing list created with the column display names are exactly matching with the below script, Here is how to import XML into SharePoint list:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
#Set these two variables accordingly
$WebURL = "https://project.crescent.com/"
$ListName = "External Projects"
$XMLFilePath = "D:\data\ExternalProjects.xml"
#Get the Web
$web = Get-SPWeb $WebURL
#Get the List
$ProjectList = $web.Lists[$ListName]
#import xml file
[xml]$ProjectXmlFile = Get-Content $XMLFilePath
foreach ($XMLProject in $ProjectXmlFile.projects.project)
{
$NewProject = $ProjectList.Items.Add()
$NewProject["Project ID"] = $XMLProject.id
$NewProject["Description"] = $XMLProject.description
#Set the People Picker Field
$NewProject["Project Manager"] = $web.EnsureUser($XMLProject.manager)
$NewProject["Cost"] = $XMLProject.cost
$NewProject["Start Date"] = $XMLProject.startdate
$NewProject.Update()
Write-Host "Project $($XMLProject.id) has been Added to External Projects list!"
}
That’s all! we are done importing XML data to SharePoint list with PowerShell!!
If you are looking for a way to export SharePoint list items to XML, Refer: Export SharePoint List to XML using PowerShell
I’ve finally made your script to work properly. Now I’m adapting a different XML file as follows:
Then I have the following script in Powershell:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
#Set these two variables accordingly
$WebURL = “https://testsite”
$ListName = “XML_Projects”
$XMLFilePath = “D:DataXML_Projects3.xml”
#Get the Web
$web = Get-SPWeb $WebURL
#Get the List
$ProjectList = $web.Lists[$ListName]
#import xml file
[xml]$ProjectXmlFile = Get-Content $XMLFilePath
foreach ($XMLProject in $ProjectXmlFile.rss.item)
{
$NewProject = $ProjectList.Items.Add()
$NewProject[“title”] = $XMLProject.title
$NewProject[“link”] = $XMLProject.link
#Set the People Picker Field
#$NewProject[“Project Manager”] = $web.EnsureUser($XMLProject.manager)
$NewProject[“description”] = $XMLProject.description
$NewProject[“pubDate”] = $XMLProject.pubDate
$NewProject[“guid”] = $XMLProject.guid
$NewProject[“dc:creator”] = $XMLProject.dccreator
$NewProject[“author”] = $XMLProject.author
$NewProject.Update()
Write-Host “Project $($XMLProject.id) has been Added to External Projects list!”
}
And I got this in every single SharePoint item list :
title
System.Xml.XmlElement
link
System.Xml.XmlElement
description
System.Xml.XmlElement
pubDate
Mon, 07 Sep 2020 06:26:48 +0200
guid
System.Xml.XmlElement
dc:creator
author
System.Xml.XmlElement
The only one working is “pubDate” because it’s plain text not using
Hi, I’ve addapted your script to a different XML file. It does not give me any errors but it doesn’t add the items to the SharePoint list. Do I have to create a custom list with the fields in it? (I’ve done that too but no luck at all)
Thanks!
Yeah, I’ve created it but still doesn’t work. I have this in the script:
foreach ($XMLProject in $ProjectXmlFile.projects.project)
{
$NewProject = $ProjectList.Items.Add()
$NewProject[“title”] = $XMLProject.title
$NewProject[“link”] = $XMLProject.link
#Set the People Picker Field
#$NewProject[“Project Manager”] = $web.EnsureUser($XMLProject.manager)
$NewProject[“description”] = $XMLProject.description
$NewProject[“pubDate”] = $XMLProject.pubDate
$NewProject[“guid”] = $XMLProject.guid
$NewProject[“dc:creator”] = $XMLProject.dc:creator
$NewProject[“author”] = $XMLProject.author
$NewProject.Update()
Write-Host “Project $($XMLProject.id) has been Added to External Projects list!”
}
and created one field per list with those names but still doesn’t work 🙁 Any ideas why?
Of course, You must create the list with columns exactly mapped!
Hi
Is it possible for SharePoint Online? Let me know your comments on importing data from XML to SharePoint Online.
Thanks
Yes! You can use PowerShell-CSOM. Refer: SharePoint Online: Import CSV File into SharePoint List using PowerShell-CSOM