Update list item from an XML file in SharePoint

Question

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

Answer 1

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.
   
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:

• Create an External Content Type in SharePoint Designer.
• External List Limitations in SharePoint 2010/2013.

---

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()

        }
    }
}