In my previous post I was describing a solution to provision SharePoint lists dynamically and also to set their permissions. However, I want to share as well the solution that will let you to create list items using XML data.

THE IDEA

The goal of this solution is to allow for a dynamic creation SharePoint list items using XML data as input data. So that columns existing in SharePoint list will be populated with related data from the XML. With just couple of steps!

Imagine that you have an XML, which is a result of a filled in online form, and that you need to store its data in SharePoint list. In the best scenario, each field in XML has a corresponding column in the SharePoint list. But what if not? There are two scenarios:

1. The data from the field should not be saved to SharePoint,
2. A new column should be dynamically provisioned to store the data from the field.

In this post, I would like to focus on the first scenario.

Why?

Well, because what I am about to present you is a solution that is very elastic and that does not require any “Apply to each” loops. So that if a column for a field is not present, then it simply must be added by the list owner, but until then process can still be completed successfully in just couple of seconds.

HOW THE DATA IS SAVED TO SHAREPOINT?

I am using the method to create item and populate its field in a single call, by using the (in my opinion) underestimated endpoint:

_api/web/lists/GetByTitle('LIST NAME')/AddValidateUpdateItemUsingPath
(Source: Working with lists and list items with REST | Microsoft Learn).

he endpoint not only allows you to create items within SharePoint list subfolders (which the regular create item endpoint can’t do), but as well to make it really fast.

MAP XML TO JSON

First step is to get the data from XML and map it to a plain JSON format, that can be the directly used in a request to the SharePoint REST endpoint. The XML structure may be flat or nested. In this case, the structure is simple:

<root>

<field-one>Some value</field-one>

<field-two>Some other value</field-two>

<some-other-field>Some another value</some-other-field>

…

</root>

To get that, I am first converting text from the XML file from base64 to a valid XML and then I am retrieving all nodes from the XML file. Finally, converting it to a valid JSON:

json(xpath(xml(base64ToString('XML FILE CONTENTS')), '/root/*'))

REMOVE NON-EXISTING FIELDS

Next, to avoid errors when the JSON is sent to the endpoint, I need to remove these fields, which are not present as columns, in the list. To achieve it, first I am making a call to the endpoint:

_api/web/lists/getbytitle('LIST NAME')/fields/?$select=Title&$filter=Hidden eq false and ReadOnlyField eq false

To get a list of all custom columns, which are present on the list:

Also, in this scenario, for the simplicity, all values are stored as text. However, you can read about saving different data types in my other post here.

Next, I need to remove from the JSON object created from XML these properties, that do not map to any column on the list and create a JSON, that could be used as the request body when creating new item. That JSON must have the following structure:

I am doing this by using a “Select” action and an expression, that checks if a property exists in the list of all columns:

This is being done by using the following expression:

if(contains('LIST_OF_COLUMNS', xpath(item(), 'name(/*)')), json(concat('{"FieldName": "', xpath(item(), 'name(/*)'), '", "FieldValue": "', if(not(empty(first(xpath(item(), '//text()')))), replace(first(xpath(item(), '//text()')), '"', '\"'), first(xpath(item(), '//text()'))), '"}')), '')

Next, process is removing entries for which there is no matching column. However this leaves empty values in the array, what still can lead to errors, when data will be used to create an item. To get rid of those empty elements, I am using the “Filter” action:

After that, the JSON is ready to be used as the request body that will be sent to the endpoint described in the beginning of the post:

Request URL: _api/web/lists/GetByTitle('LIST NAME')/AddValidateUpdateItemUsingPath
Request body:

{
"listItemCreateInfo": {
"FolderPath": {
"DecodedUrl": "ABSOLUTE PATH TO THE LIST"
},
"UnderlyingObjectType": 0
},
"formValues": [{"fieldName":"field name", "fieldValue": "field value"}, …],
"bNewDocumentUpdate": false
}

NEXT STEPS?

There are two possible next steps in this solution:

1. Notify whomever the owner of the list is that there is data that hasn’t been saved, because the following columns were not present, or
2. Create the missing columns.

Let me try to show you each one of them, as they are quite similar.

HOW TO RETRIEVE LIST OF MISSING COLUMNS?

This is the crucial step in the whole solution – process needs to find the list of columns, that are present as nodes in XML file, but are missing from the list. To do that, you just need to make a revert expression – that removes a column from the JSON with all data from XML, if it IS PRESENT in the list. This is done by simply adding not() expression to the condition. The expression is following:

if(not(contains('LIST_OF_COLUMNS', xpath(item(), 'name(/*)'))), json(concat('{"FieldName": "', xpath(item(), 'name(/*)'), '", "FieldValue": "', if(not(empty(first(xpath(item(), '//text()')))), replace(first(xpath(item(), '//text()')), '"', '\"'), first(xpath(item(), '//text()'))), '"}')), '')

Next, as in the steps described above, process needs to get rid of all empty values. To do that I will use the same “Filter” action as above.

And now this is where you have to make the choice – notify or create. If just notify, then simply make a nice looking HTML list and send info about missing columns using e-mail. To prepare the list use the following “Select” action:

And then in the “Send an email” action wrap its outcomes using the following HTML code:

<ul><li>join(outputs('Prepare_list_to_be_sent'), '</li><li>')</li></ul>

If you’d like to create the missing columns prior to creating the record itself, here you need to use the “Apply to each” loop over the array built above. Then in each run simply call the endpoint to create a column in the list. For the sake of simplicity, process will just create single line of text columns here, but feel free to add more logic, evaluating type of data, and trying to create more suitable columns when needed:

Endpoint URL: _api/web/lists/getbytitle(‘LIST NAME’)/fields
Request body:

{
"__metadata": {
"type": "SP.Field"
},
"Title": "FIELD NAME",
"FieldTypeKind": 2,
"Required": "false",
"EnforceUniqueValues": "false",
"StaticName": "FIELD NAME"
}

Field type kind values can be found here: FieldType enumeration (Microsoft.SharePoint.Client) | Microsoft Learn

That’s it!

WRAP UP!

With the solution described above you are able to really dynamically create records in SharePoint lists, without even thinking if the columns are present or not. This solution completes the one I described in my previous post, about dynamic provisioning of lists and permissions. I hope you like it and that it can help you 😉

About the Author

I’m Tomasz Poszytek, Microsoft Business Applications MVP. From more than 10 years now I’m being involved in projects, which aim is production of various applications using SharePoint as a platform. My adventure started absolutely without a purpose, when in the beginning of 2000 I was given FrontPage installation disc and started experimenting with this WYSWIG tool.  

Read more.

References

Poszytek, T., (2023), ”, available at: https://poszytek.eu/en/microsoft-en/office-365-en/powerautomate-en/save-xml-data-to-sharepoint-list-dynamically/, [Accessed 27th March 2024].