cancel
Showing results for 
Search instead for 
Did you mean: 

How To: Extracting XML Data from a Repeating Section

Workflow Hero
1 0 333

 

We have all ran into this scenario before: "I need to be able to submit multiple {insert thing 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 apporach 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!

 

Hands on approach

If you are looking for a hands-on approach to repeating sections, check out the blog by @eharris04  on How to get data from a form repeating section in O365.

 

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 all together.

 

Things you'll need if following along:

SharePoint list - data entry (form)

SharePoint list - create itemized list of request

Document Library - document is generated at end of workflow (used in 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 a 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.RepeatingSectionXML.PNGRepeatingSectionXML

Here is the published form (filled out):Form.PNGPublished Form

 

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:GetData from XML.PNGGet Data from Repeating Section

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

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. GetData from Collections.PNGLoop through Data and Create List Item

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

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:Create New Item specifics.PNGCreate Item in Itemized List

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:Add Item to Collection specifics.PNGAdd SP ID to Collection

 

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.DocGen Specifics.PNGDocument 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 Foxtrot RPA offering.  I will cover that part of the process next time!

 

 

Sneak preview into the next blog

Did you know that you can kick off a bot in Foxtrot via a file trigger? That's right, we can trigger a bot within Foxtrot with the document that I just created!Foxtrot File Trigger.PNGFoxtrot RPA - File Trigger