Skip to main content
Nintex Community Menu Bar
Solved

XML Cleanup to a nice format

  • July 13, 2022
  • 13 replies
  • 107 views
  • Translate
J

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

 

Best answer by Garrett

Hi @JHjalmarsson 

Sorry, there is only 1 For Each loop. I feel bad for giving you the wrong hint. So, here is the solution for extracting the XML

 

 

1. The Query XML

 

2. The For Each

 

 

 

3. Query XML Column 1 (You will need 4x)

The XPath based on your XML

  1) //c613aaf0-24e3-4e36-8f34-36c7b9ad1764
  2) //ddlAccessTypeModule
  3) //ddliFlightModule
  4) //Network_AccessLevel

 

4. Final workflow (basic)

 

Hope that helps

 

 

View original
    Did this topic help you find an answer to your question?

    13 replies

    Garrett
    Forum|alt.badge.img+16
    • Garrett
    • Scout
    • 904 replies
    • July 13, 2022

    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

    Translate
    J

     

    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. 

    Translate
    Garrett
    Forum|alt.badge.img+16
    • Garrett
    • Scout
    • 904 replies
    • July 13, 2022

    You can achieve this by using HTML Table markups

     

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

     

    Translate
    J

    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 🙂

    Translate
    Garrett
    Forum|alt.badge.img+16
    • Garrett
    • Scout
    • 904 replies
    • July 13, 2022

    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

     

    Translate
    J

    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?

    Translate
    Garrett
    Forum|alt.badge.img+16
    • Garrett
    • Scout
    • 904 replies
    • July 13, 2022

    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.

     

     

    Translate
    J

    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 

    Translate
    J

    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

    Translate
    Garrett
    Forum|alt.badge.img+16
    • Garrett
    • Scout
    • 904 replies
    • July 14, 2022

    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 ""

     

    Translate
    Garrett
    Forum|alt.badge.img+16
    • Garrett
    • Scout
    • 904 replies
    • July 14, 2022

    Hi @JHjalmarsson 

     

    Pls have a look at this article on how to create Variable with HTML tables for use in the email body

    https://community.nintex.com/t5/Blog/Workflow-Log-in-Task-forms/ba-p/210329 

     

     

    Translate
    Garrett
    Forum|alt.badge.img+16
    • Garrett
    • Scout
    • 904 replies
    • Answer
    • July 14, 2022

    Hi @JHjalmarsson 

    Sorry, there is only 1 For Each loop. I feel bad for giving you the wrong hint. So, here is the solution for extracting the XML

     

     

    1. The Query XML

     

    2. The For Each

     

     

     

    3. Query XML Column 1 (You will need 4x)

    The XPath based on your XML

      1) //c613aaf0-24e3-4e36-8f34-36c7b9ad1764
      2) //ddlAccessTypeModule
      3) //ddliFlightModule
      4) //Network_AccessLevel

     

    4. Final workflow (basic)

     

    Hope that helps

     

     

    Translate
    MillaZ
    1 person likes this
    • MillaZ
      MillaZ
    J

    Ahyhhhhhhh Thats it.. That is what I was doing wrong :-). I should have figured that out. 🙂 

     

    Thank you

    Translate

    Reply


    Sidebar Buttons
    Nintex Community Home Contact Nintex Support Email Nintex Community Check Nintex Status
    Terms & ConditionsCookie Settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie Settings