Import XML File Data into SharePoint List using PowerShell

Requirement: PowerShell to Import XML to SharePoint List
We've an utility from 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

6 comments:

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

    ReplyDelete
  2. 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!

    ReplyDelete
    Replies
    1. Of course, You must create the list with columns exactly mapped!

      Delete
    2. 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?

      Delete
  3. 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:\Data\XML_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 <![CDATA so I guess that I will have to call a different method for those fields so I'll be able to populate the fields?

    Thanks so much in advance for your support.

    ReplyDelete

Please Login and comment to get your questions answered!

Powered by Blogger.