Flow to concatenate all SP column values in a single row.

[MuFeR]

Got a simple yet tricky flow I'm stuck with.

Let's say I got the following SP list with 3 columns (all of type SingleLineOfText). 

Color	Day	Pet
Black	Monday	Dog
Red	Sunday	Cat
Yellow	Saturday	Parrot

Output i'm trying to get is all the values of each individual column separated by "/".

So for above example I want to do a "Get items" from SharePoint and then add a row in an existing excel file which should look like this.

Color	Day	Pet
Black/Red/Yellow	Monday/Sunday/Saturday	Dog/Cat/Parrot

Only way I figured out would be to initialize a string for every single column (varColor, varDay, varPet) and then do 3 "Append to string" actions in a loop to append the next value to all variables and just add the variable in the new excel row. Problem with that approach is that in reality i'm having many lists with way more columns so i'm trying to find an expression that does the trick and avoid ending up with 200 initialize/append variable actions in a single flow.

[v-dezhili-msft]

Hi @MuFeR ,

I have a test for your reference.

Best Regards,

Dezhi

[grantjenkins · Accepted solution]

This requirement aligns to using XPath perfectly.

My list used in this example:

And my Excel Table:

See my full flow below. I'll go into each of the actions.

Get items retrieves all the items from my SharePoint List.

XML is a Compose action that converts the body of Get items to XML so that we can use XPath in the next step. It also adds a root element to ensure its valid XML. The expression used here is:

xml(json(concat('{"root": ', outputs('Get_items')?['body'], '}')))

And finally, we have our Add a row into a table which takes in the following expressions for Color, Day, and Pet respectively. This extracts out each of the values and joins them with a /

join(xpath(outputs('XML'), '//root/value/Color/text()'), '/')

join(xpath(outputs('XML'), '//root/value/Day/text()'), '/')

join(xpath(outputs('XML'), '//root/value/Pet/text()'), '/')

And the output after the flow completes, we have the values joined, separated by /

[MuFeR]

Does the xml expression only work with the Get_items action or could it also be modified to work with Filter Array?

I tried to use
xml(json(concat('{"root": ', body('Filter_array'), '}')))

but i get the error "Unable to process template language expressions in action 'XML' inputs at line '0' and column '0': 'The template language function 'xml' parameter is not valid. The provided value cannot be converted to XML: 'This document already has a 'DocumentElement' node.'."

[grantjenkins · Accepted solution]

You'd just need to wrap the array into a property as so:

xml(json(concat('{"root": { values:', body('Filter_array'), '}}')))

[MuFeR]

Thanks this worked perfectly!
I also had to modify the next part from:
'//root/value/Color/text()' to '//root/values/Color/text()'