nintjex
Nintex Newbie

How to parse xml from a nested repeating section

Hi

 

I've been trying different ways to parse xml from a repeating section that is within a repeating section but I've hit a wall. I've posted the full xml as jpg and the actions and their details as a word file with screenshots. Sorry if that is not how you're supposed to do it I'm new to this.

 

Anyway I followed this solution from here but to no avail:

https://community.nintex.com/t5/Nintex-for-SharePoint/Get-data-out-of-Nested-Repeating-Section-and-H...

 

 

 

0 Kudos
Reply
3 Replies
allan
Nintex Newbie

Re: How to parse xml from a nested repeating section

There's no other way than replacing all the encoded characters with their non-encoded characters actually.
You can use a replace string to do that :

  • & --> & (do this first)
  • > --> >
  • &lt; --> <
  • &quot; --> "
  • etc.

It is a pain to do it, but you cannot use xmldecode, which does not exists.

Tags (1)
Reply
Chris_Ben
Automation Master
Automation Master

Re: How to parse xml from a nested repeating section

Hi, you should be able to parse the nested repeating section but might have to run the fn-xmlDecode function a few times.  Can you please upload your XML as text rather than an image?  We'll give it a go with your exact XML.

0 Kudos
Reply
Chris_Ben
Automation Master
Automation Master

Re: How to parse xml from a nested repeating section

I had a bit of spare time so I tried to recreate your form - this is a good example of managing repeating sections within repeating sections so hopefully a good resource for many people.  The key is the repeating sections are stored as XML but some manipulation is needed to get the XML that you need.  I must say, this posed an additional fun challenge in trying to read the XML you provided which is in Norwegian or Danish (I couldn't figure out which).  Read on!

 

I constructed a form that would provide an output very similar to yours except I didn't include your third repeating section, RS_Kommunikation as it would be a repeat of the example provided below.

 

01 filled form.png

 

For people new to this post, we have two repeating sections.  The first repeating section, I bind to a multiline SharePoint column that I've called "the xml". Inside that repeating section there are is another repeating section called RS_Handlingspunkter.   This is what the raw XML looks like once the form data is saved to the "the xml" column:

 

<?xml version="1.0" encoding="utf-8"?><RepeaterData><Version /><Items><Item><Kvt_Valg type="System.String">1</Kvt_Valg><Kvt_Beskrivelse type="System.String">This is the main repeating section, row 1</Kvt_Beskrivelse><RS_Handlingspunkter type="System.String">&amp;lt;?xml version=&amp;quot;1.0&amp;quot; encoding=&amp;quot;utf-8&amp;quot;?&amp;gt;&amp;lt;RepeaterData&amp;gt;&amp;lt;Version /&amp;gt;&amp;lt;Items&amp;gt;&amp;lt;Item&amp;gt;&amp;lt;Handlingspunkt_Nr type=&amp;quot;System.Int32&amp;quot;&amp;gt;11&amp;lt;/Handlingspunkt_Nr&amp;gt;&amp;lt;Handlingspunkt_Titel type=&amp;quot;System.String&amp;quot;&amp;gt;RS1 Item 1&amp;lt;/Handlingspunkt_Titel&amp;gt;&amp;lt;Handlingspunkt_Beskrivelse type=&amp;quot;System.String&amp;quot;&amp;gt;Apple&amp;lt;/Handlingspunkt_Beskrivelse&amp;gt;&amp;lt;/Item&amp;gt;&amp;lt;Item&amp;gt;&amp;lt;Handlingspunkt_Nr type=&amp;quot;System.Int32&amp;quot;&amp;gt;12&amp;lt;/Handlingspunkt_Nr&amp;gt;&amp;lt;Handlingspunkt_Titel type=&amp;quot;System.String&amp;quot;&amp;gt;RS1 Item 2&amp;lt;/Handlingspunkt_Titel&amp;gt;&amp;lt;Handlingspunkt_Beskrivelse type=&amp;quot;System.String&amp;quot;&amp;gt;Orange&amp;lt;/Handlingspunkt_Beskrivelse&amp;gt;&amp;lt;/Item&amp;gt;&amp;lt;/Items&amp;gt;&amp;lt;/RepeaterData&amp;gt;</RS_Handlingspunkter><Formal_fortaelling_indsats type="System.String"></Formal_fortaelling_indsats></Item><Item><Kvt_Valg type="System.String">2</Kvt_Valg><Kvt_Beskrivelse type="System.String">This is the main repeating section, row 2</Kvt_Beskrivelse><RS_Handlingspunkter type="System.String">&amp;lt;?xml version=&amp;quot;1.0&amp;quot; encoding=&amp;quot;utf-8&amp;quot;?&amp;gt;&amp;lt;RepeaterData&amp;gt;&amp;lt;Version /&amp;gt;&amp;lt;Items&amp;gt;&amp;lt;Item&amp;gt;&amp;lt;Handlingspunkt_Nr type=&amp;quot;System.Int32&amp;quot;&amp;gt;21&amp;lt;/Handlingspunkt_Nr&amp;gt;&amp;lt;Handlingspunkt_Titel type=&amp;quot;System.String&amp;quot;&amp;gt;RS2 Item 1&amp;lt;/Handlingspunkt_Titel&amp;gt;&amp;lt;Handlingspunkt_Beskrivelse type=&amp;quot;System.String&amp;quot;&amp;gt;Pear&amp;lt;/Handlingspunkt_Beskrivelse&amp;gt;&amp;lt;/Item&amp;gt;&amp;lt;/Items&amp;gt;&amp;lt;/RepeaterData&amp;gt;</RS_Handlingspunkter><Formal_fortaelling_indsats type="System.String"></Formal_fortaelling_indsats></Item></Items></RepeaterData>

It's pretty ugly and it has lots of things making it hard to read such as &amp.  Therefore the first thing we need to do in the workflow is decode the XML to get a "clean" version using the runtime function fn-XMLDecode inside the build string action:

02 decode xml.png

 

I perform this twice as just to be safe.  Don't ask me why but sometimes the function misses decoding some parts as I found out when I was testing the workflow.  This is what the XML looks like after is has been cleaned up for easy reading:

 

<?xml version="1.0" encoding="utf-8"?>
<RepeaterData>
   <Version/>
   <Items>
      <Item>
         <Kvt_Valg type="System.String">1</Kvt_Valg>
         <Kvt_Beskrivelse type="System.String">This is the main repeating section,row 1</Kvt_Beskrivelse>
         <RS_Handlingspunkter type="System.String">
         <?xml version="1.0" encoding="utf-8"?>
            <RepeaterData>
               <Version/>
               <Items>
                  <Item>
                     <Handlingspunkt_Nr type="System.Int32">11</Handlingspunkt_Nr>
                     <Handlingspunkt_Titel type="System.String">RS1 Item 1</Handlingspunkt_Titel>
                     <Handlingspunkt_Beskrivelse type="System.String">Apple</Handlingspunkt_Beskrivelse>
                  </Item>
                  <Item>
                     <Handlingspunkt_Nr type="System.Int32">12</Handlingspunkt_Nr>
                     <Handlingspunkt_Titel type="System.String">RS1 Item 2</Handlingspunkt_Titel>
                     <Handlingspunkt_Beskrivelse type="System.String">Orange</Handlingspunkt_Beskrivelse>
                  </Item>
               </Items>
            </RepeaterData>
         </RS_Handlingspunkter>
         <Formal_fortaelling_indsats type="System.String"/>
      </Item>
      <Item>
         <Kvt_Valg type="System.String">2</Kvt_Valg>
         <Kvt_Beskrivelse type="System.String">This is the main repeating section,row 2</Kvt_Beskrivelse>
         <RS_Handlingspunkter type="System.String">
         <?xml version="1.0" encoding="utf-8"?>
            <RepeaterData>
               <Version/>
               <Items>
                  <Item>
                     <Handlingspunkt_Nr type="System.Int32">21</Handlingspunkt_Nr>
                     <Handlingspunkt_Titel type="System.String">RS2 Item 1</Handlingspunkt_Titel>
                     <Handlingspunkt_Beskrivelse type="System.String">Pear</Handlingspunkt_Beskrivelse>
                  </Item>
               </Items>
            </RepeaterData>
         </RS_Handlingspunkter>
         <Formal_fortaelling_indsats type="System.String"/>
      </Item>
   </Items>
</RepeaterData>

 

It's still not perfect though as then we need to remove the three xml headers (the text <?xml version="1.0" encoding="utf-8"?>). These are included before each repeating section as you can see above. The Xpath engine gets confused by the multiple headers so we need to remove them.  We'll use a regular expression to do this:

03 reg ex.png

 

Now it's up to you how you want to deal with the data. For example if you wanted to save all the Handlingspunkt_Nr values into a single collection, you could use the Query XML action, use the expression

RepeaterData/Items/Item/RS_Handlingspunkter/RepeaterData/Items/Item/Handlingspunkt_Nr

and return the results as text.

 

The issue with this approach is you're combining all the results from both the main and the RS_Handlingspunkter repeating sections. I think you'd rather handle them on a row by row basis. Therefore my query XML action looks like this:

04 save rows to a collection.png

 

A couple of points to note here:

  1. I'm splitting by each row of the main repeating section hence me using the expression
    RepeaterData/Items
  2. We need the results returned as XML because you'll want to parse the RS_Handlingspunkter repeating section separately and that is stored as XML. I have therefore chosen to return the results as Outer XML. Select Outer XML vs Inner XML because you want the opening and closing <Item> tags to create a well formed XML structure.

In this example we now have a collection called col Items with two items:
Item 1

<Item>
   <Kvt_Valg type="System.String">1</Kvt_Valg>
   <Kvt_Beskrivelse type="System.String">This is the main repeating section,row 1</Kvt_Beskrivelse>
   <RS_Handlingspunkter type="System.String">
      <RepeaterData>
         <Version/>
         <Items>
            <Item>
               <Handlingspunkt_Nr type="System.Int32">11</Handlingspunkt_Nr>
               <Handlingspunkt_Titel type="System.String">RS1 Item 1</Handlingspunkt_Titel>
               <Handlingspunkt_Beskrivelse type="System.String">Apple</Handlingspunkt_Beskrivelse>
            </Item>
            <Item>
               <Handlingspunkt_Nr type="System.Int32">12</Handlingspunkt_Nr>
               <Handlingspunkt_Titel type="System.String">RS1 Item 2</Handlingspunkt_Titel>
               <Handlingspunkt_Beskrivelse type="System.String">Orange</Handlingspunkt_Beskrivelse>
            </Item>
         </Items>
      </RepeaterData>
   </RS_Handlingspunkter>
   <Formal_fortaelling_indsats type="System.String"/>
</Item>

Item 2

<Item>
   <Kvt_Valg type="System.String">2</Kvt_Valg>
   <Kvt_Beskrivelse type="System.String">This is the main repeating section,row 2</Kvt_Beskrivelse>
   <RS_Handlingspunkter type="System.String">
      <RepeaterData>
         <Version/>
         <Items>
            <Item>
               <Handlingspunkt_Nr type="System.Int32">21</Handlingspunkt_Nr>
               <Handlingspunkt_Titel type="System.String">RS2 Item 1</Handlingspunkt_Titel>
               <Handlingspunkt_Beskrivelse type="System.String">Pear</Handlingspunkt_Beskrivelse>
            </Item>
         </Items>
      </RepeaterData>
   </RS_Handlingspunkter>
   <Formal_fortaelling_indsats type="System.String"/>
</Item>

 

Now that each line has been saved to a collection entry, we are able to process the elements inside it. Use a for each loop to do this and save the item to a text variable:

05 for each.png

 

For example, if we want to save all the Beskrivelses for the current row (which Google tells me means "Description" then use another Query XML using the expression

/Item/RS_Handlingspunkter/RepeaterData/Items/Item/Handlingspunkt_Beskrivelse

06 query xml.png

Note in this case how I have returned the results as Text as I don't want the XML tags around the data.

 

If you wanted to perform row by row manipulation of your RS_Handlingspunkter, then use Query XML with the expression /Item/RS_Handlingspunkter and save to a collection. Then you can use a for each loop to go through each element of the collection.

 

I've saved an export of the workflow here which also emails you some of the results - be sure to change your email address in the first set a variable action.

07 workflow.png

I hope that helps!

 

Cheers,

Chris

 

 

0 Kudos
Reply