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.

powershell import xml 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  = "http://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

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

6 thoughts on “Import XML File Data into SharePoint List using PowerShell

  • September 9, 2020 at 11:29 AM

    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 = “http://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 Reply

  • September 8, 2020 at 11:52 AM

    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!

    Reply
    • September 8, 2020 at 3:20 PM

      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?

      Reply
  • November 4, 2016 at 12:43 PM

    Hi
    Is it possible for SharePoint Online? Let me know your comments on importing data from XML to SharePoint Online.
    Thanks

    Reply

Leave a Reply