XML Cleanup to a nice format

Hi Everyone,

Have built a workflow that looks at a multi line column in SP 2013 which holds a repeating table data. I have got to the point where I have the xml and can query it. However it just looks awful when I use the variable in an email.

First i clean it by using fn-XmlDecode({ItemProperty:TestColumn})

Then i regex and take out the <?xml version="1.0" encoding="utf-8"?>

Then i Query the clan xml using Xpath //RepeaterData and store it as Text

Then a for each to loop through all the items

Then a collection operation to "get" the data

And I store it and use the variable in an email

This is the XML

<RepeaterData>
<Version />
<Items>
<Item>
<c613aaf0-24e3-4e36-8f34-36c7b9ad1764 type="System.String">Test details
</c613aaf0-24e3-4e36-8f34-36c7b9ad1764>
<ddlAccessTypeModule type="System.String">Open Plan
</ddlAccessTypeModule>
<ddliFlightModule type="System.String">Select
</ddliFlightModule>
<Network_AccessLevel type="System.String">Select
</Network_AccessLevel>
</Item>
<Item>
<c613aaf0-24e3-4e36-8f34-36c7b9ad1764 type="System.String">Test detyaoil;2
</c613aaf0-24e3-4e36-8f34-36c7b9ad1764>
<ddlAccessTypeModule type="System.String">Stuff
</ddlAccessTypeModule>
<ddliFlightModule type="System.String">HR
</ddliFlightModule>
<Network_AccessLevel type="System.String">Select
</Network_AccessLevel>
</Item>
<Item>
<c613aaf0-24e3-4e36-8f34-36c7b9ad1764 type="System.String">Online detals
</c613aaf0-24e3-4e36-8f34-36c7b9ad1764>
<ddlAccessTypeModule type="System.String">SharePoint
</ddlAccessTypeModule>
<ddliFlightModule type="System.String">Select
</ddliFlightModule>
<Network_AccessLevel type="System.String">Select
</Network_AccessLevel>
</Item>
</Items>
</RepeaterData>

When I use the data in an email it comes out like below. How can i massage this into lines

Test detailsOpen PlanSelectSelectTest detyaoil;2Stuff MROHRSelectOnline detalsSharePointSelectSelect

Its my first time actually querying XML and i got this far but that is it

Any help would be awesome

Page 1 / 1

Hi @JHjalmarsson , can you screenshot the Email Body with the variables?

Are you just using a single variable in your email body?

You can also store the value into a variable at specific location

Type the message in Notepad or any text editor and paste it into the variable field (this is how to get the newline char into your variable). Add your {var} at the desired location.

Is this what you want to accomplish?

Place each extracted XML value into a new line

Hope that helps

Thank you so much for coming back to me. Yes at the moment I'm using one variable. I want to be able to put in the email something like the screenshot but in text.

You can achieve this by using HTML Table markups

Instead of assign plain text to your {Message} variable, you assign HTML Table markup codes.

I think what I'm struggling with is separating out the nodes into rows. I just get it all in one bunch at the moment 🙂

Could you share your screen for the workflow - XML Query

What is your XML Query settings especially the XPath?

You need to have 2 foreach loop.

- LOOP1: [//Item] - This get each ROW

- LOOP2: then in each Row, you need to get the value of each COLUMN

ahhhh I have my Xpath to //RepeaterData but not a second one saving to a multi line collection.

So i need a second one that is Xpath //RepeaterData/Items/Item/Access Details and store that in a second multi line collection?

You will need 1 XML Query for the ROW

In LOOP2:

You will need 1 XML Query for each COLUMN

So another 4 XML query for the 4 column values.

Ok i can see where this is going. I need to clean up my collection names and variables. This will be tomorrows work. Now the last one is I guess how to tie it all together.

You have put me on the right path i think. I'm just a little confused with the loops but will figure it out.

Thank you so much.

Ill be back

ok have changed the workflow to the below hard to see but i'm doing 4 for each using

//RepeaterData/Items/Item

It actually works but it only pulls one record

And the result in the email

Using 4 different results variables. i think i'm closer

Hi @JHjalmarsson

How log does it takes you from starting until completing the workflow (1 cycle)? 20min?

To save time, I perform the XML using PowerShell (you can use any programing language)

Once I get the Query and XPath correct, then I convert to Nintex Workflow.

## POWERSHELL CODE


## FILENAME: XML_EXAMPLE.PS1


## AUTHOR  : GARRETT





## Extract XML EXAMPLE (JHjalmarsson)


[xml]$xmlRawSource = '<RepeaterData><Version /><Items><Item><c613aaf0-24e3-4e36-8f34-36c7b9ad1764 type="System.String">Test details</c613aaf0-24e3-4e36-8f34-36c7b9ad1764><ddlAccessTypeModule type="System.String">Open Plan</ddlAccessTypeModule><ddliFlightModule type="System.String">Select</ddliFlightModule><Network_AccessLevel type="System.String">Select</Network_AccessLevel></Item><Item><c613aaf0-24e3-4e36-8f34-36c7b9ad1764 type="System.String">Test detyaoil;2</c613aaf0-24e3-4e36-8f34-36c7b9ad1764><ddlAccessTypeModule type="System.String">Stuff</ddlAccessTypeModule><ddliFlightModule type="System.String">HR</ddliFlightModule><Network_AccessLevel type="System.String">Select</Network_AccessLevel></Item><Item><c613aaf0-24e3-4e36-8f34-36c7b9ad1764 type="System.String">Online detals</c613aaf0-24e3-4e36-8f34-36c7b9ad1764><ddlAccessTypeModule type="System.String">SharePoint</ddlAccessTypeModule><ddliFlightModule type="System.String">Select</ddliFlightModule><Network_AccessLevel type="System.String">Select</Network_AccessLevel></Item></Items></RepeaterData>'


## Extract Row "//Item"


$rows = $xmlRawSource.selectNodes("//Item")





CLS


## Process Rows


foreach ($row in $rows) {


    ## Extract Columns


    $Unknown              = $row.'c613aaf0-24e3-4e36-8f34-36c7b9ad1764'.InnerText


    $AccessTypeModule     = $row.ddlAccessTypeModule.InnerText


    $FlightModule         = $row.ddliFlightModule.InnerText


    $Network_AccessLevel  = $row.Network_AccessLevel.InnerText


    Write-Host "-----------------------" 


    Write-Host "  Unknown     : $Unknown"


    Write-Host "  AccessType  : $AccessTypeModule"


    Write-Host "  FlightModule: $FlightModule"


    Write-Host "  NetAccess   : $Network_AccessLevel"





}


Write-Host "-----------------------" 


Write-Host ""