2

I have an XML file of currency conversions: https://www.dropbox.com/s/jhv6ms2v6s796z0/Screenshot%202013-11-29%2009.51.46.png

I need to pull in a couple of these live to a Sharepoint list (this XML file is updated daily). So in my list I would have one column for currency e.g. SEK - Sharepoint would then use that column to populate the 'rate' column, retrieving the corresponding value for SEK on that list and populating it, with an update daily.

Is this something that can be easily achieved? What's the best way to go about it?

  • Just being able to pull it into a workflow would be very ideal, actually. But even if I get as far as adding it as a data source for a site in SPD, I then have no clue how to use it's data. – Henry Cole Nov 29 '13 at 9:56
  • You can use PowerShell to do this. Is it a one time action? Is this xml file on disk? – Remko Nov 29 '13 at 10:16
  • @Remko not automated though surely. Ideally I need it to sit and update daily, or to trigger when a workflow is run, or something like that. Also new to Powershell admin, any pointers? – Henry Cole Nov 29 '13 at 10:17
  • Added a example how it could be done – Remko Nov 29 '13 at 10:48
2

You can achive it by using business data list connector for sharepoint 2010, after downloading and installing it, try with the following steps,

1.Create your list in SharePoint, e.g. a custom list named XML. Tipp: Use an appropiate list type, e.g. contacts for address-based information etc.

2.Goto List Settings > General Settings > Connect to external data source. If you don't have this link, please activate the Business Data List Connector for the current site collection.

3.Select the Layer2 Data Provider for XML as connection provider.

4.Enter the following settings as Connection String: e.g. URL=http://www.w3schools.com/xml/cd_catalog.xml alternatively you can use XML files, e.g. URL=C:\myfile.xml. Validate the connection string.

5.Enter the following XPath settings as Select Statement: e.g. select * from /CATALOG/CD[COUNTRY="USA"] Validate the select statement. Preview your data. Take a look at the content: Are there unique columns to use as primary key? In this case Title and Artist should be unique together.

6.Enter the Primary Key, "Title, Artist" in this sample. The primary key allows to savely update items later on, in case your source data changes. Validate your primary key.

7.Klick on "Add Columns" to create the list structure for hosting the external data. Please check mapping. Mapping is done automatically by internal SharePoint column names and data source field names. You can change column titles and column types (e.g. currency) later on.

8.Klick update for first insert. Please note that this could take a while, depending on data amount and configuration. If done, click update again. It should be much faster, and there should be no changes (based on primary keys given).

9.You can now enter some final options about background update, e.g. scheduling etc. if required.

10.Don't forget to save your connection with "OK".

Source

OR

If you want to do it by code, you can create a console application as mentioned in the below link, create a windows task and execute the console application "exe' to update the list.

Console application populate SharePoint List from XML

  • Thanks for the advice, but sadly I'm on 365 so no such luck. Will give the application solution a go. – Henry Cole Nov 29 '13 at 13:37
  • Hey karthik can you please send me console application link b'ce above link not work. – Admin Nov 11 '16 at 10:44
0

If a list should be filled with data on a scheduled base, you could use PowerShell in combination with the task scheduler. Create a PowerShell file to read the xml and loop though it. Then populate the list with the data.

You're PowerShell could look something like this

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 = "<url web>"
$listTitle = "<list title>" 
$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()

        }
    }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.