Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
0 like 0 dislike
1.5k views
in SharePoint Server by 3 4 6

How can I update a SharePoint list automatically with the data from the XML file on a daily basis?

I am thinking to use the SharePoint Designer Workflow to do this? is it possible?

Please tell me the best way to implement this task


1 Answer

0 like 0 dislike
by 86 158 330

inserts items into a specific SharePoint list from an XML file

There are multiple options to get data from an XML file and save it in SharePoint List.

But, it actually depends on your requirements that not clear in your question.

Let me provide you the common scenarios that may match your requirements.

Get data from XML file to SharePoint List Using External Content-Type

If you don't implement any logic or calculation on the XML data and you just need to show data from XML file in SharePoint list, so you can

  1. Create your own WCF service.
  2. Use External Content-Type to consume it.
    External data source Type Selection
  3. Use the External List to show your data.

You must configure Bussiness connectivity service configured to can use External Content Type and External List. check how to configure BCS.

For more details, please check the following:


Get data from XML file to SharePoint List Using Timer Job

If you would like to implement some calculation on the XML data before adding it to the SharePoint List, so you should use SharePoint Timer Job.

You can read data from XML file in C# using the below code:

DataSet xmlDS = new DataSet();
xmlDS.ReadXml("the Xml FilePath");
DataTable xmlDT = xmlDS.Tables[0];

Loop for each item in xmlDT and add it to the SharePoint List as the following:

using (SPSite oSite=new SPSite("http://your sharepoint"))
    {
        using (SPWeb oWeb=oSite.RootWeb)
        {
            SPList oList = oWeb.Lists["Test"];
            foreach(DataRow row in xmlDT.Rows)
              {
                 SPListItem oSPListItem = oList.Items.Add();
                 oSPListItem["Title"] = xmlDT["Field Name"].ToString();;
                 oSPListItem.Update();
              }
        }  
  }

Get data from XML file to SharePoint List Using PowerShell

You can also create a task schedule that runs PowerShell script on a specific time as you prefer.

The below script inserts items into a specific SharePoint list from an XML file.

Add-PsSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue -WarningAction SilentlyContinue | Out-Null 
[System.Xml.XmlDocument] $XmlDoc = new-object System.Xml.XmlDocument
$path = "<path to xml>"
$XmlDoc = [xml](Get-Content $path)

$url = "<your site>"
$listTitle = "<your list name>" 
$web = Get-SPWeb $url
$list = $web.Lists.TryGetList($listTitle)
if ( $list -ne $null)
    {

    $XmlDoc.gesmes.Cube.Cube | % {
        $cube = $_
        $cubeTime = $cube.time
        $cube.Cube | % {
            $cubeEntry = $_
            $currency = $cubeEntry.currency
            $rate =  $cubeEntry.rate

            $item  =$list.AddItem();
            $item["<CurrencyField>"] = $currency
            $item["<RateField>"] = $rate
            $item.Update()

        }
    }
}
If you don’t ask, the answer is always NO!
...