How To: Extracting XML Data from a Repeating Section

How to create an itemized list of all data within a repeating section on your form. In this blog we will discuss how to leverage Query XML, Loops, Collections, and Document Generation actions.

We have all ran into this scenario before: “I need to be able to submit multiple (insert things to submit here) but in one single form.” This is not a new concept of having a form that allows for multiple items within it, nor is the idea of how to go about collecting that data anything new. However, there does seem to be some different approaches on what to do with the data AFTER it has been submitted. Here is how I would approach this scenario, but please, let me know what you think in the comments below and what you would change or how you would go about architecting a solution like this. Let’s jump in!

Scenario

I have a form that I use to request banking information. I know the bank name and location, but need to know additional information for each one. Rather than fill out a form for each bank, I can use a repeating section to capture each request and process the entire form altogether.

Things you’ll need if following along:

• SharePoint list – data entry (form)
• SharePoint list – create an itemized list of request
• Document Library – the document is generated at end of a workflow (used in the second part) – more to come on this!

Form

I am not going to go into great detail on how to design the form, but there is one specific piece that needs to be addressed. In my SharePoint list, I have multiple lines of text column named RepeatingSectionXML. This colum will be used in the form and connected to the repeating section. This allows me to capture all of the data that is being entered into the repeating section and usable within my workflow.

Here is the published form (filled out):

Workflow

Now that the form is setup and we are collecting the XML data in a list column, we can move on the building out our workflow to break it apart and push it to an itemized list.

First step: Get Data from Repeating Section XML

I use a parallel action to get all of the elements I need and store them into collection variables. For the above banking example, it will look like this:

And here is a closer look at the Query XML actions:

Be sure your XPath query is accurate. I recommend using something like https://www.freeformatter.com/xpath-tester.html to test and validate your query.

Second step, now that we have all the needed data points in collections, is to simply loop through them and create a new item in our itemized list in SharePoint.

We begin our loop by pulling out data for each Bank and then getting the data from the Location collection at the same index:

At this point we have both the Bank Name and Location that was requested and can send it to the Itemized list for further processing. This is as simple as leveraging the Create Item in List action setup like this:

You’ll notice that we are storing the newly created SharePoint item ID in a variable so that we can save it and use later. Here, we may also want to consider adding some more content to the new item as we are creating it. Perhaps the {currentItem:ID}? If we add a reference to the “parent” item (the form we filled out in the beginning), we can perform lookups later on in another workflow or process to ensure updates happen if needed. Last step is to add the item ID to a collection and we can do so like this:

At this point, we have filled out a form to request additional information on some banks. This data was stored in a repeating section (XML) and we used that data to create an itemized view in another list (via query XML, looping, and create item actions). The main focus was how to parse through the XML and do something with it. For this scenario, we broke it apart and send each item to a new list. From here, you can replace all of the actions inside the loop with whatever actions you need to perform for your specific process/workflow.

In this example, we have one more step; Document Generation.

I am generating an Excel sheet of all of the Bank Names, Locations, and SharePoint IDs that will be used for lookup the additional required data. The reason I am doing this is because the information that needs to be referenced is on the FDIC website and will be scraped using out Nintex RPA offering.